Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bsethi24
Starting Member
25 Posts |
 Posted - 2013-12-17 : 08:59:53
|
| 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. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-12-17 : 10:03:31
|
| How do you know that the 2:30 is for the select? I'm guessing you commented out the final select part and it was 2:30 seconds faster?Have you run the SP in a query window are are you only calling it from an application? Because 30 columns of 200K rows is a lot to load in any kind of display. Are you sure that isn't what is taking up the time?Be One with the OptimizerTG |
 |
|
|
bsethi24
Starting Member
25 Posts |
Posted - 2013-12-17 : 12:15:13
|
| HI! Yes 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. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-12-17 : 14:22:28
|
| weird - please post the CREATE TABLE statement (DDL) of your temporary table including any constraints, PKs, or indexes. And post the actual "simple select" statement that's taking 3 minutes. EDIT:one other thing - see if this speeds things up:SELECT <your columns>INTO #newTempFROM <yourTempTable>see if is it the actual streaming of the data to the results pane or the select out of the table.Be One with the OptimizerTG |
|
|
|
bsethi24
Starting Member
25 Posts |
Posted - 2013-12-18 : 07:50:24
|
| Hi! Yes, inserting data into another table is not taking this much of time. But, the problem is we need to use SELECT <Column Names> from <Table Name> only as it will go to a dataset for remaining process. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2013-12-18 : 13:24:20
|
| Is the 3 minutes just the time it takes to physically transmit the data, for 200,000 rows x 30 column, from the server to your workstation?Try changing the "simple select" to only select a single INT column. If that runs in a few seconds then you are limited by the sheer size of the data. Exclude any columns from the SELECT that the application does not actually need, make sure there are no trailing SPACES (that the application does not need). Or speed up your network!! |
|
|
|
|
|
|
|
|
Improve SIMPLE SELECT to retun 2 LAC Rows faster