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
>