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