December 23, 2008 at 10:22 am
Hi,
A audit table is having more than 4 crores of records. I am trying to fetching the first 3 lakhs records using the following command
select top(300000) * from Audit :hehe:
. But its taking more than 35 minutes in SQL Query Analyzer. How to reduce the execution time.... 😀
Regards,
Vijay
December 23, 2008 at 11:34 am
From Books On Line (BOL)
If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.
(Emphasis added)
Since you do not have an ORDER BY clause but state
the first 3 lakhs
what is it that you are trying to achieve?
If you post the table structure and provide a limited amount of sample data as per the aricle in my signature block someone will be able to assist you.
(Are we to understand that a Lakhs is one hundred thousand (100,000) and that a crore is 100 Lakhs?)
December 23, 2008 at 1:40 pm
December 23, 2008 at 4:35 pm
Please post the query plan also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 23, 2008 at 7:21 pm
Hi,
My Query is Pretty simple. A aduit table is having day to day transactions of the Student Table manipulation information. In that i need to display the past one year audit information. i used the following query
select * from [Audit] where CreatedWhen between GetYear() and GetYear()-365
Its returning 3lakhs of records, but it taking more than 35 minutes to display the entire result.
Guide me how to improve the performance...
Regards,
Vijay 🙂
December 23, 2008 at 7:34 pm
How long does this command take?
select *
Into #temp
from [Audit]
where CreatedWhen between GetYear() and GetYear()-365
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 23, 2008 at 9:00 pm
For the above select into statement executes with 15 seconds but while i gave like select * from #temp its again taking more than 35 minutes.
December 23, 2008 at 10:27 pm
Seems to me that most of that time is just the client trying to display that much data. Why would you need to return 300,000 rows of data to the screen?
December 23, 2008 at 10:38 pm
i need to display output in the crystal report.
December 24, 2008 at 6:38 am
Why would you need to display 300,000 rows in a crystal report? Who is going to go through that much data? This is starting to seem like a case of something you can do vs. something you should do. A year's worth of audit data in a crystal report?
Would it be possible to add some additional filters, like for only one entity, or for only certain events... or some aggregated information over all that data.
December 24, 2008 at 8:41 am
Yes, like Seth, I suspect the transmission time. It's either your network or your Client that is too slow for that much data, not your SQL Server.
This highlights a long-standing problem with the way that Crystal and many other report generators work: Instead of leveraging the capabilities of their data servers, they prefer to just pull all of the data over to the client and use their own logic on it there. So if, for instance, you want Crystal to summarize 300,000 rows of data for you, instead of executing a GROUP BY for you on the server and bring back only 3000 rows, Crystal will try to bring back all 300,000 rows and then do the summarization logic itself.
This tends to be very, very bad, performance-wise.
Because of this, I always follow this rule with report generators: Always write the summary logic yourself in a view or stored procedure on the server and then have the Report Generator call that instead.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 24, 2008 at 8:55 am
RBarryYoung (12/24/2008)
Yes, like Seth, I suspect the transmission time. It's either your network or your Client that is too slow for that much data, not your SQL Server.
I was pretty sure that's where you were going with that question before, which is the only reason it occurred to me. So really, the credit is yours, not mine :).
This highlights a long-standing problem with the way that Crystal and many other report generators work: Instead of leveraging the capabilities of their data servers, they prefer to just pull all of the data over to the client and use their own logic on it there. So if, for instance, you want Crystal to summarize 300,000 rows of data for you, instead of executing a GROUP BY for you on the server and bring back only 3000 rows, Crystal will try to bring back all 300,000 rows and then do the summarization logic itself.
This tends to be very, very bad, performance-wise.
Because of this, I always follow this rule with report generators: Always write the summary logic yourself in a view or stored procedure on the server and then have the Report Generator call that instead.
Oh yeah, I'm so used to just working around this that I forgot the reason I always work around it :hehe: (And I find it much easier to just write the SQL than try to do the Crystal Formatting). Taking this one step further, if you want to crash your server, try to join that table to another table... in Crystal.
December 24, 2008 at 10:24 am
The irony of course is that Report Generators are supposed to isolate the end-user from technical issues like this so that they do not have to know about them. Instead they tend to exacerbate these problems.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply