December 17, 2013 at 7:03 am
Hi Team,
We have a Store Procedure and in this SP we are creating a TEMPORARY Table which has more than 200000 rows with 30 columns (2 of datetime, 4 of INT, 5 of [numeric](20, 3) and rest of VARCHAR(500) data types. This SP is giving the SIMPLE SELECT of this TEMPORARY Table in final output.
Now the problem is the final SELECT statement is taking more than 3 Minutes to list all records.
We tried by adding an Identity column to this Temp. Table and added Non-Clustered Index on this Identity column and in final output we don't include this column in SELECT statement but, in ORDER BY clause only. But, still it is taking more than 2.30 Minutes.
We need a solution to get this "SELECT" statement output faster.
Please advice.
December 17, 2013 at 7:07 am
We can help you but you need to provide way more information for us first. Please see this article about best practices when posting performance issues. 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/
December 17, 2013 at 10:20 am
HI! As per my understanding posted almost all info. about the problem. Please let me know what exactly you required to solve this problem. I am really eager to have the solution :).
Also please note that I took the SP in SSMS and executed individual queries and found that the Final SIMPLE SELECT statement is taking more than 3 Minutes to list all records.
Please advice.
December 17, 2013 at 10:28 am
bharat sethi (12/17/2013)
HI! As per my understanding posted almost all info. about the problem. Please let me know what exactly you required to solve this problem. I am really eager to have the solution :).Also please note that I took the SP in SSMS and executed individual queries and found that the Final SIMPLE SELECT statement is taking more than 3 Minutes to list all records.
Please advice.
I assume that you didn't bother to read the article I suggested. Ask yourself if you honestly think you would be able to provide anything resembling solid advice based on what you posted. I can't see your screen, I have no idea what your tables and indexes are like, I have no idea what the actual query you are running looks like, I have no idea how many rows are in the tables, I have no idea if your stats are current, to summarize, I have no idea how to help you.
At the absolute bare minimum you need to post an actual execution plan. Including ddl for the tables and indexes would be a big help too.
From your brief description of what you are doing I would ask why you need to copy rows from a table into a temp table to simply then select those rows back out. But without more details about what you are actually doing I can't really guess on that either.
_______________________________________________________________
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/
December 17, 2013 at 10:42 am
Hi!
This SP has
an initial query to load data in temp. table with 5 tables joins. This query executed within 15 seconds.
Then have 7 update queries to update diff. diff. columns of this Temp. table from diff. diff. tables.
Each of these updates executed within 5-10 seconds.
Then we have the SIMPLE SELECT statement to return all the rows (207958) of this TEMP. table.
Temp. table don't have Indexes. That's why tried to add Identity column and Non-Clustered Index on it and
in execution plan of this SIMPLE SELECT statement SQL is doing TABLE SCAN.
Will attach the snap of Execution Plan soon.
December 17, 2013 at 10:51 am
bharat sethi (12/17/2013)
Hi!This SP has
an initial query to load data in temp. table with 5 tables joins. This query executed within 15 seconds.
Then have 7 update queries to update diff. diff. columns of this Temp. table from diff. diff. tables.
Each of these updates executed within 5-10 seconds.
Then we have the SIMPLE SELECT statement to return all the rows (207958) of this TEMP. table.
Temp. table don't have Indexes. That's why tried to add Identity column and Non-Clustered Index on it and
in execution plan of this SIMPLE SELECT statement SQL is doing TABLE SCAN.
Will attach the snap of Execution Plan soon.
Of course it is a table scan. You are selecting all the rows from it. Remember that you are first writing and retrieving 207958 rows of data. Most of the time being spent here is most likely disc I/O.
Temp. table don't have Indexes.
You could add them but I don't think it will help in this case because you are selecting all the rows so an index isn't going to help here.
_______________________________________________________________
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/
December 18, 2013 at 5:48 am
Hi! Then how do we improve it?
December 18, 2013 at 6:04 am
bharat sethi (12/18/2013)
Hi! Then how do we improve it?
if you are selecting all rows(we still didn't see the query, which might be improved), then you can't speed it up with indexing;
so it comes down to amount of data returned, and the underlying hardware.
do you really need to return 207958 rows of data?
if it's being used to populate a web page, the data may be returned very quickly, but the creation and rendering of all the TD / TR tags takes any browser a TON of time to build a page...you need to identify where they "slowness" is occurring.
can you change it so it provides an intelligent summary of the information, instead of all the data? what are you doing with the data?
do you really need every column in the query? are you returning image/text/varchar(max) fields? are they even being used if they are returned?
is the slowness on the LAN, or over the internet? is your LAN gigibit speed?
Lowell
December 18, 2013 at 7:18 am
Hi! The data is not going to a web page. It's a SSIS package which process this task and then provide this data to sucessor tasks or other processes. there is no image/text columns (pl. refer previous replies for columns list) and H/w had a gigbit LAN only.
The query is Select <column names> from <Temp.TableName>.
December 18, 2013 at 7:26 am
Try this link. It will be a great starting point for helping us find you an answer.
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
_______________________________________________________________
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/
December 18, 2013 at 7:45 am
bharat sethi (12/18/2013)
Hi! The data is not going to a web page. It's a SSIS package which process this task and then provide this data to sucessor tasks or other processes. there is no image/text columns (pl. refer previous replies for columns list) and H/w had a gigbit LAN only.The query is Select <column names> from <Temp.TableName>.
ok, nine posts into this thread, and now you tell us this is part of an SSIS package? don't you think you might have been relevant to the question at hand? the first post implies it's slow, but without identifying much more than that.
if it's in an SSIS package, why does it have to be fast? doesn't the data simply need to be processed, regardless of whether it takes one second or one minute?
help us help you.
remember, we are not standing in your cubicle, looking over your shoulder; we only know what you post here. A lot of us have an incredible amount of experience, and volunteer our time to help others.
if you can provide details, we can probably help, but so far, you haven't done your part on providing anything other than a textual description of the issue.
so far, I've understood that you are returning 200K rows of data, and it's a pretty large dataset, and it takes "more than 3 Minutes to list all records."
how is it you see it takes 3 minutes to "list" them?
in ssms by running the stored procedure call?
for any of us to diagnose anything, we need concrete things like a description of the process, and what is actually going wrong; any specific error numbers raised; stored procedure definitions, table definitions, indexes on the relevant tables, actual execution plans; anything that might identify to us the pain points you might be experiencing.
the proc definition might be relevant for example, if you were using a linked server, or any of a number of other things i can think of performance wise.
Hope you can help us help you!
Lowell
December 26, 2013 at 2:15 pm
Without looking at details/specifics,
you can try to get a 'blind' performance gain by using @Table table type variable instead of
#temporary table. You can gain performance improvement to some extent by avoiding writing/reading to/form disk
because table type variable is processed in memory. But with a large number of rows it may present another problem,
depending on how much available memory your server has and you do need to look ant the query exec plan, no doubt.
Likes to play Chess
December 26, 2013 at 2:21 pm
vladisaev@hotmail.com (12/26/2013)
Without looking at details/specifics,you can try to get a 'blind' performance gain by using @Table table type variable instead of
#temporary table. You can gain performance improvement to some extent by avoiding writing/reading to/form disk
because table type variable is processed in memory. But with a large number of rows it may present another problem,
depending on how much available memory your server has and you do need to look ant the query exec plan, no doubt.
Note that temp tables can reside in memory and table variables can be written to disk if needed.
http://sqlserverplanet.com/tsql/yet-another-temp-tables-vs-table-variables-article
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply