July 7, 2009 at 11:44 pm
Hi All,
In OLEDB command source i am using sql command option to pull the records from the table. the table contains huge amount of data and i have to take based on max(effective_date) logic
For example;Select secalis,coupon,sectype from secmaster sm where sm.effective_date=(select max(effective_date) from secmaster sm1 where sm1.secalias=sm.secalias)
and sm.src_intfc_inst=(select instance from interface where short_desc='dd')
the above query approximately pull 35000 records. likewise i have to pull records from 13 tables and finally i have to merge based on secalis value.
The above said process is taking too much time to complete.
Can someone guide how to tune this process?
Regards
Senthil
July 8, 2009 at 8:53 am
Well, I'm not sure you provided enough information to give you a good answer. So here are some questions I can think of:
Are all the tables in the same database or in databases on the same server? If, yes, you would probably be better off using a UNION in T-SQL to merge the data.
Have you run the queries in SSMS and looked at the execution plans to see if there is a way to improve the queries by eliminating scans?
Here is a modification of the one query you posted, that I think will perform better, but you will need to test it against your data and compare it to your current query:
Select
secalis,
coupon,
sectype
from
secmaster sm JOIN
(select
sm1.secalias,
max(effective_date) AS max_date
from
secmaster sm1
GROUP BY
sm1.secalias
) AS dates
sm.secalias = dates.secalias AND
sm.effective_date = dates.max_date
WHERE
EXISTS (SELECT 1 FROM interface WHERE short_desc = 'dd' AND instance = sm.src_intfc_inst)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply