November 22, 2012 at 5:06 am
I have some 20000000 rows in my database. While fetching records on some criteria it take too much time. I created all possible indexing also but still it took more than 3 min. How can i improve performance of my Sp.
In Sp I am using Temp table also and lot of join between those temp table.
Will it improve performance if i create views by joining few tables and then fetch data from those views.
Regards
Priti
November 22, 2012 at 5:17 am
What you want to do after fetching 20000000 rows? Do you want to display all rows the screen?
Regards,
Vijay
November 22, 2012 at 5:29 am
priti 19979 (11/22/2012)
I have some 20000000 rows in my database. While fetching records on some criteria it take too much time. I created all possible indexing also but still it took more than 3 min. How can i improve performance of my Sp.In Sp I am using Temp table also and lot of join between those temp table.
Will it improve performance if i create views by joining few tables and then fetch data from those views.
Regards
Priti
20,000,000 rows fetching to where? Screen? Another table? File?
Have you checked what takes this time? Try:
SET STATISTICS IO ON
SET STATISTICS TIME ON
To see what takes what...
November 22, 2012 at 5:51 am
I have 20000000 records in table1 which i joined with another table on some criteria and computing the value and then aggregating all the records and displayed it into screen
Example
Insert into #tmpTable1
Select A.Column1*B.Column1 from Table1 A inner join Table2 B on A.column2 =B.Column2
Insert into #tmpTable2
Select * from #tmpTable1 inner join Table3 on ....
select sum(column1) from #tmpTable2
Above is brief example which i used in my sp. But my SP is more complex than this. because i used 6 temp table and joined some 5-6 table . I temp table i am storing data after some calculation and condition and again joined temp table with some other table
finally i have to aggregate all filtered records. Which will be fast if i used views or temp table
Regards
Priti
November 22, 2012 at 6:52 am
...if i used views or temp table
...
From you have said, you already using temp tables.
Without looking into real code, it's impossible to suggest anything specific.
Try using views and see if it helps you...
November 22, 2012 at 7:43 am
Please post the actual complete procedure, table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 22, 2012 at 9:10 am
I have 20000000 records in table1 which i joined with another table on some criteria and computing the value and then aggregating all the records and displayed it into screen
From what you've posted it looks like you are using temp tables to make the procedure more comprehensible, rather than for any business logic, with that many records you might end up with loads of IO activity that will slow you down.
There is alot to consider here, dropping the temp table approach might be one, using Indexed Views can also speed things up when reading data, but at this point its just shooting in the dark unless you provide exactly what is happening in your query.
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply