April 27, 2015 at 10:30 am
Hi there,
I have been assigned to optimize a query which was taking 16 hours to run. So worked around and brought it down to 32 minutes. (for data testing, i was loading the in a table as final step rather than selecting)
so that went well but when now we are querying the table, which has 85 columns and 3 million records, it alone is taking more than 2 hour to return the data. The developers have told me that 85 columns are all must have and they cant tweak it.
Is there anything i can do to make it run faster.
I have removed the final step of the inserting in the table and now it is actually a select, it is still taking same amount of time.
Thank in advance.
April 27, 2015 at 10:33 am
neeraj-344433 (4/27/2015)
Hi there,I have been assigned to optimize a query which was taking 16 hours to run. So worked around and brought it down to 32 minutes. (for data testing, i was loading the in a table as final step rather than selecting)
so that went well but when now we are querying the table, which has 85 columns and 3 million records, it alone is taking more than 2 hour to return the data. The developers have told me that 85 columns are all must have and they cant tweak it.
Is there anything i can do to make it run faster.
I have removed the final step of the inserting in the table and now it is actually a select, it is still taking same amount of time.
Thank in advance.
Hi and welcome to the forums. In order to help we need a LOT more information. Just asking if you can make a query faster isn't going to elicit anything useful. At the VERY LEAST posting the query is required. Please take a few minutes and take a look at this article. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 27, 2015 at 10:34 am
They say that all 85 columns are needed, but how many rows of data are being returned as well?
April 27, 2015 at 10:37 am
@SSChampion - At this point, it is basically select * from table. This is table has 85 columns. Why i am saying this is because the query to load the data into this table finishes within 32 minutes (which is acceptable).
I need to know how to deal with the resultset with all the columns.
@Lynn - Yes, all of them has values.
thanks guys for the support!
April 27, 2015 at 10:41 am
neeraj-344433 (4/27/2015)
I need to know how to deal with the resultset with all the columns.
Not sure what that means. It is acceptable that the query takes 32 minutes to load. I have no idea what you want to do the results of your query so it is difficult to offer any advice.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 27, 2015 at 10:48 am
You have a table with 85 columns and 3M rows. Do you need to query all of them every time?
April 27, 2015 at 10:52 am
It is a nightly ETL job. Once the data is loaded in the staging table, the process picks it up and loads into other system.
April 27, 2015 at 10:54 am
Please answer Sean's question. 'To deal' is not a well defined verb in the T-SQL world.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 27, 2015 at 10:56 am
neeraj-344433 (4/27/2015)
It is a nightly ETL job. Once the data is loaded in the staging table, the process picks it up and loads into other system.
That's the 16-hour run you got down to 32 minutes, right? Nice job, BTW. The query that takes 2 hours is different, right?
We need to understand what you mean by "deal with the result set with all the columns" in order to understand how to help.
April 27, 2015 at 11:00 am
neeraj-344433 (4/27/2015)
@SSChampion - At this point, it is basically select * from table. This is table has 85 columns. Why i am saying this is because the query to load the data into this table finishes within 32 minutes (which is acceptable).I need to know how to deal with the resultset with all the columns.
@Lynn - Yes, all of them has values.
thanks guys for the support!
I didn't ask if all the columns had data, I asked how many rows of data are being returned to the user.
April 27, 2015 at 11:01 am
My apologies, if i am not clear about this.
Yes, you are right, the problem now is the select * from Table query. This table has 85 columns and 3 million records. Most of the columns in this table are int and about 10 are varchar(1000).
No index on any column as there is no filter criteria.
I hope that helps.
Again, I appreciate the help on this.
April 27, 2015 at 11:04 am
Risking sounding repetitive, but...
What does this mean?
I need to know how to deal with the resultset with all the columns.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 27, 2015 at 11:08 am
"To deal" means I am asking for suggestion about How to make it faster. It is the slow performing query now. Select * from Table.
April 27, 2015 at 11:15 am
Will it help if I clustered index on the table by adding a identity column to it. As of now, its a heap because there is no index and we are doing the select * without any filter.
Just a thought?
April 27, 2015 at 11:36 am
What exactly are you doing with the data, writing it to a file? Is there an application pulling it in one row at a time?
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply