so in the product table we have 2 million records..so what is the best way to replace the product sub query
Can you cut down your table ? archive the data or can you do table partition?
Are you using Indexing well in your tables?
Can you use Full Text search?
COlumns in the join condition are the datatype of varchar, so does this decrease the performance
Yes it does so
Can you change your datatype to numeric type (tinyint , int bingint decimal?
query is very big...it has like 100 columns and when i select only one column the query is fast but when i select 100 columns the query speed is slow.
Query has 100 columns
i mean really All 100 columns are required in one report.
less columns less overhead, more column more overhead.
(Select ProductDesc from Product Where ProductID = rt.productid), --subquery 1
(select ProductnaME FROM PRODUCT WHERE PRODUCTID = RT.PRODUCTID) -- subquery2
tABLE1 T1 INNER JOIN TABLE2 T2 ON T1.COL = T2.COL
Remove the SUbquery and join those tables.
Neeraj Prasad SharmaSql Server Tutorials