Hi Naomi,
It seems I did it without attention again.. the index was created on SQL Server directly, and when I did data deployment using strataframe I lost it.. so performance now ok.
but by the way question

About picture_count and pdf_count - I need here just a flag is picture exist or is pdf exist, so count is not real needed. but.. I found the way for this just using count :)))

About top(100) - it was generated by designer on SQL Server.. not sure its decrease performance of query

and about index.. yes.. I lost it.. and this was a problem :((....

>1. Why do you need select top (100) percent - would it be the same as select ?
>
>2. If you remove for now Picture_Count and Pdf_Count - really weird way to get these counts - can you get some improvement in speed?
>
>3. I assume you have an index on the manufacturer_pk_id field - the name suggests it should be a PK field, when why you're grouping?
>
>Can you post table structure and idexes?
>
>>Hi,
>>I have a stored procedure for search.. one of them..using SQL Server FullTextSearch and for 500 000 records its take some second to return results.. another.. simple WHERE statement.. but it take more then minute..
>>The question - can I optimize query? or why it take too long?
>>
>>In both queries diffrence only in WHERE .. and exec time.. first one take more then 1 minute, second just 1,5 sec.
>>
>>This code take more then minute
>>

>>ELSE IF @tcSearchType='010'
>>/* SEARCH BY MANUFACTURER ONLY */
>>SELECT     TOP (100) PERCENT MAX(pk_recid) AS pk_recid, MAX(herstnr) AS herstnr, MAX(hersteller) AS hersteller, 
>>COUNT(herstnr) AS itemcount, AVG(einst_prs) AS avg_prs, 
>>                      MIN(einst_prs) AS min_prs, MAX(einst_prs) AS max_prs, MAX(art_bez1) AS art_bez1, MAX(ean) AS ean,
>>                          (SELECT     COUNT(*) AS cnt
>>                            FROM          dbo.vbestbuy_content
>>                            WHERE      (content_type = 0) AND (herstnr = MAX(dbo.vbestbuy.herstnr))) AS picture_count,
>>                          (SELECT     COUNT(*) AS cnt
>>                            FROM          dbo.vbestbuy_content AS vbestbuy_content_1
>>                            WHERE      (content_type = 1) AND (herstnr = MAX(dbo.vbestbuy.herstnr)))  AS pdf_count
>>FROM         dbo.vbestbuy
>>WHERE (dbo.vbestbuy.manufacturer_pk_id=@tnLieferantId AND @tnLieferantId IS NOT NULL)
>>GROUP BY hestnr_ean
>>ORDER BY herstnr
>>

>>
>>
>>This query - only one and half second (FULLTEXTSEARCH)
>>

>>ELSE IF @tcSearchType='001'
>>/* SEARCH FULLTEXTSEARCH ONLY*/
>>SELECT     TOP (100) PERCENT MAX(pk_recid) AS pk_recid, MAX(herstnr) AS herstnr, MAX(hersteller) AS hersteller, 
>>COUNT(herstnr) AS itemcount, AVG(einst_prs) AS avg_prs, 
>>                      MIN(einst_prs) AS min_prs, MAX(einst_prs) AS max_prs, MAX(art_bez1) AS art_bez1, MAX(ean) AS ean,
>>                          (SELECT     COUNT(*) AS cnt
>>                            FROM          dbo.vbestbuy_content
>>                            WHERE      (content_type = 0) AND (herstnr = MAX(dbo.vbestbuy.herstnr))) AS picture_count,
>>                          (SELECT     COUNT(*) AS cnt
>>                            FROM          dbo.vbestbuy_content AS vbestbuy_content_1
>>                            WHERE      (content_type = 1) AND (herstnr = MAX(dbo.vbestbuy.herstnr)))  AS pdf_count
>>FROM         dbo.vbestbuy
>>WHERE (CONTAINS(dbo.vbestbuy.fulltextsearch,@tcSearchString) )
>>GROUP BY hestnr_ean
>>ORDER BY herstnr
>>