Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Creating temporary table
Message
From
04/02/2014 21:32:55
 
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Environment versions
SQL Server:
SQL Server 2008
Application:
Web
Miscellaneous
Thread ID:
01593184
Message ID:
01593230
Views:
34
>It doesn't have to be a temp table, it can be a CTE, e.g.

CTE results in the same situation.

However, here is something interesting.

This is beyond whatever I had imagined as far as boosting performance on what I have.

We know that the first stage to collect the keys is immediate. As I am only selecting the primary keys, with all the conditions, including JOIN conditions, this has proven to be immediate until now and up to several tens of thousands of records.

The remaining part to optimize was to collect the fields necessary to show in the list. Trying to do it all in one SQL was way out of proportion.

My framework tells me the number of records per table, if it is higher than 1000, then I go in this special logistic.

1. Collect the keys as mentioned above
2. Start to display the list, usually 50 or 100 records at a time
3. For every primary key, do another select to get the fields needed where the WHERE clause is Table.PrimaryKey=that primary key

For 50 to 100 records, this is all processed in 0.000 second. The only difference is that is sollicitates more SQL Server but the performance is completely in a different scale.

I have a very solid framework and well designed. So, I only had to do a few changes here and there.

The problem is resolved and very welcomed.

Thanks for your input, the more brainstorming, the more it helps to find new things to try.
Michel Fournier
Level Extreme Inc.
Designer, architect, owner of the Level Extreme Platform
Subscribe to the site at https://www.levelextreme.com/Home/DataEntry?Activator=55&NoStore=303
Subscription benefits https://www.levelextreme.com/Home/ViewPage?Activator=7&ID=52
Previous
Reply
Map
View

Click here to load this message in the networking platform