View returns huge data,taking more time

gunjan.sh · March 27th, 2008, 11:24 PM

Hi,

I have a view which returns around thirty Lakh records. And is taking more time to execute.
The View has around 11 tables in which some of the tables are left joined .. which is resulting in a huge data .....
Is there any way i can make the execution faster?

Kindly help.

Thanks.

Gunjan

planoie · March 28th, 2008, 08:10 AM

What is "Lakh"?

Have you optimized the table with indexes? Do they all need to be joined with LEFT joins? Is all the data you are getting the data you actually want?

-Peter
peterlanoie.blog

Jeff Moden · March 28th, 2008, 06:46 PM

Lakh = million

--Jeff Moden

Jeff Moden · March 28th, 2008, 06:47 PM

Quote:

quote:Originally posted by gunjan.sh
Hi,

I have a view which returns around thirty Lakh records. And is taking more time to execute.
The View has around 11 tables in which some of the tables are left joined .. which is resulting in a huge data .....
Is there any way i can make the execution faster?

Kindly help.

Thanks.

Gunjan

Indexes may help but only if the query in the view is "sargeable". Post the code.

--Jeff Moden

gunjan.sh · March 30th, 2008, 05:44 AM

Hi,
I need all the data returned by doing left joins ...
And have sufficient indexes on the base Tables ...
But still it's getting timed out because of the huge data returned.

Thanks.

Gunjan

planoie · March 30th, 2008, 11:52 AM

Then I'm not sure you'll be able to get more out of it. Have you profiled and tested your query to see if there is something in there that could be optimized?

-Peter
peterlanoie.blog

gunjan.sh · March 31st, 2008, 11:24 PM

I did run the profiler ... but that also was of no help ....

Gunjan

Preethi · April 1st, 2008, 04:15 AM

You may have a couple of options
1. Do you need all the data at once? If not, divide the data into multiple chunks and receive them one by one. One possible option (I don't know your data but making a wild guess) is breaking the data based on the year (or month if you have only one year's data) or year and month. You may execute the query for every month. To do this in a few you need to add a temp table with only one row having the start date and end date You can loop through to get the whole data

2. Increase query timeout. You can sent it to 0 and the application will wait until SQL Server responds

G.R.Preethiviraj Kulasingham

gunjan.sh · April 1st, 2008, 05:02 AM

Can u please tell me how to increase the time out period ?

Thanks in advance.

Gunjan

Jeff Moden · April 1st, 2008, 06:48 AM

Increasing the time out period is, at best, a short term patch... not a fix. You'll probably find the real problem in the code in the view... the view probably needs to be rewritten but no one can tell because the code for the view has not been posted.

--Jeff Moden