Why the following problem arise in Table Variable case...Expert Must See this problem.
-
Hi to all there is an article in Sqlservercentral.com , which show me the use of while loop insteand of Cursor . for using while loop we use the Table Variable. But using Table variable the following problem arise... What should i do for that .......................
Is there any other alternative to fast the sp without using cursor......?????
I try all these code of using while loop instand of cursor.
but there is a problem i feel that when u apply the while loop in your stored procedure it work better and give fast result as compare to Cursor. But when i run my stored procedure and see the Execution Plan in the query analyzer. i see a red color in the temp Variable Table Scan .
It give the informtion Bad use of Statistics in the Table. and the table scan cost become 100% in each round. for this i use # table and create a Clustered Index on this table and i solve this problem. but my question is that why table scan cost goes to 100% and come a red color message bad use of Statistics for table scan......
Is there any other alternative of using table variable and how should i avoide such table scan problem....
-
Hi,
In general, is there a performance difference between using table variables and using temporary tables in SQL Server 2000?
There can be a big performance difference between using table variables and temporary tables. In most cases, temporary tables are faster than table variables. I took the following tip from the private SQL Server MVP newsgroup and received permission from Microsoft to share it with you. One MVP noticed that although queries using table variables didn't generate parallel query plans on a large SMP box, similar queries using temporary tables (local or global) and running under the same circumstances did generate parallel plans.
A senior member of the SQL Server development team told me that table variables use internal metadata in a way that prevents the engine from using a table variable within a parallel query. He also said that SQL Server maintains statistics for queries that use temporary tables but not for queries that use table variables. Without statistics, SQL Server might choose a poor processing plan for a query that contains a table variable. The development team member added that you should limit your use of SQL Server 2000 table variables to reasonably small queries and data sets and use temporary tables for larger data sets. . .
cheers
-
The main reason to use table variables is to skip object creation and recompiles. Quite rightly table variables do not maintain stats and although you can add a PK the queries will always scan. However for small datasets table variables are great and can perform well avoiding recompilation, which can be quite critical in a large or often used proc.
As I say table variables always scan and may not choose a good join plan, I have lots of good examples with client code but as yet have not been able to create test data to illustrate the differences, ( requires million rows test tables etc. )
Best rule is tune to suit. Which performs best in testing. Keep table variable datasets small.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Thans Both of you,
you have given me the right ans. when i watch the stat of the table variable query that's way i say so in my question , one more question from both, if i create a cluster or non cluster index on the temp table. and first i insert the 100000 records in this temp table. and after that i select records one by one by avoiding cursor. is it better to use index on such temp tables.
because indexing create Insertion slow.......
Ten Centuries if u don't mind send me some SP's of such kind which use Temp table for while loop instand of Cursor.....
I will be very thank full to u , or if u have any web site link which give's more such kind of tips then send in your next post,...................
-
you may find the index is not required if you're then going to select * from the temp table. Table and table variables work just the same in loops.
generally adding an index afterwards may be quicker, all depends what you're hoping to achieve. Test and see is the only way to find out.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
GO
SET SHOWPLAN_ALL ON
GO
DECLARE @demo TABLE (ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED)
INSERT INTO @demo DEFAULT VALUES
INSERT INTO @demo DEFAULT VALUES
INSERT INTO @demo DEFAULT VALUES
INSERT INTO @demo DEFAULT VALUES
INSERT INTO @demo DEFAULT VALUES
SELECT * FROM @demo WHERE ID BETWEEN 3 AND 4
/*
SELECT * FROM @demo WHERE ID BETWEEN 3 AND 4 17 11 0 NULL NULL 7 NULL 1.0 NULL NULL NULL 6.4082001E-3 NULL NULL SELECT 0 NULL
|--Clustered Index Seek(OBJECT
@demo), SEEK@demo.[ID] >= 3 AND @demo.[ID] <= 4) ORDERED FORWARD) 17 13 11 Clustered Index Seek Clustered Index Seek OBJECT@demo), SEEK@demo.[ID] >= 3 AND @demo.[ID] <= 4) ORDERED FORWARD @Demo.[ID] 1.0 6.3284999E-3 7.9600002E-5 11 0.0064081 @Demo.[ID] NULL PLAN_ROW 0 1.0*/
GO
SET SHOWPLAN_ALL OFF
GO
-
yeah I did think I didn't really mean to say that after posting < grin > !!! quite often in joins however where it's not a singleton join they tend to scan rather than seek, more so than a tmp table , however I rarely store more than a couple of pages data in a table variable so it's netiher here nor there.
If I can ever recreate test data I have some really nice examples of table variables doing unexpected things!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
It is recommended to use table variables only for small amounts of data. Both temporary tables and table variables are held in memory while there is space to do so and both are written to disc when memory runs short so there is not much gain in i/o. Table variables don't generate any entries in tempdb, however, so there is less risk of resource contention. On the other hand, table variables don't allow parallel execution and can give poor performance on multiprocessor machines as mentioned above.
-
Another benifit of table vairable is that there is no tran log for actions on table variable. When you rollback a transaction, the data in a table variable is still there.
For temp table, all actions (insert, update, delete) are logged.
So you have lots data manipiulations, table variables may have better performance.
Based on my tests and experiences (up to 20000row size), table variable is much faster.
-
As in all things "it just depends", joining against table variables which hold , say, several thousand rows can give some strange results.
Best is always to test and test and test again. And then test again just to make sure!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply