June 18, 2006 at 10:59 pm
I've a report to be generated. It needs data to be fectched from major transaction tables in the system. Currently, it is done with a long query of about 300 lines, with a list of EXISTS (SELECT..... ) filter conditions in the WHERE clause and a list of inner SELECT queries for the computations and aggregations that needs to be done in SELECT clause.
I strongly feel that the performance can be improved, by implementing it with tables variables or temp tables, by taking the first cut data from transaction tables and then applying the filters and calculations on the temp table. That way transaction tables can be released faster too.
Some of the disadvantages I found abt using temp tables were :
1. SQL Server is not good in iterative operations;
2. Scalability issues
3. Table variables are inappropriate for a large recordsets - after they
take so much memory they convert to some sort of temp tables.
4. Query optimizer to be less involved in the execution
Could anyone give an expert opinion on which approach would be better?
Thanks & Regards,
Bismi
June 19, 2006 at 2:47 am
It really depend's on your query & the table which you are accessing. if you are selecting data from a huge table then tmp table is the good opction as that will cut down the time & prosess of applying the filters and calculations on the main table.
We use tmp tables very frequently and still manage to get all the data in time with out any problems the only thing we make sure the tempdb size should not grow much between the week as every weekend we have a shedule server restart which truncates the tempdb to the normal size.
------------
Prakash Sawant
http://psawant.blogspot.com
June 19, 2006 at 4:03 am
POST THE CODE
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 20, 2006 at 10:06 am
You should look at using derived tables.
(i.e. Select MyField from MyTable m1 inner join (select keyfrom MyTable where blah) m2 on m1.Key=m2.Key)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply