Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Indexed table variables.

    Yo everybody.

    I can put a primary key on a table variable to speed up processing, but does anybody know if it is or is not possible to add additional non-clustered indexes on a table variable? I can't find any way to do it, yet I can't find anything in Books Online that says specifically that it can't be done.

    blindman

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Doesn't seem to like it...

    Code:
    DECLARE @x TABLE (Col1 int, col2 char(1))
    
    ALTER TABLE @x WITH NOCHECK ADD 
    	 PRIMARY KEY  CLUSTERED 
    	(
    		Col1
    	)
    GO
    But why would you want to? It's all in memory.

    If you've got that much data, won't that be a problem?

    Also, if you've got data in a "work" table datatype (since it doesn't stick around when the thread is done) aren't you going to basically "scan" the tab;e every time?

    Won't it be the driver of a process, and if not, why would you move data from 1 place to another and not use it?

    Just curious.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm trying to make a frankenquery more efficient by reducing the size of the recordset. The problem is that many of the criteria and joins utilize secondary indexes on the main table, and so the query actually runs more slowly when I use a reduced recordset without these additional indexes.

    Unfortunately, I can't use temporary tables because these procedures will be called by multiple users through Crystal Enterprise which uses the same connection for all users. The only way I can see to keep these user's scopes separate is to use table variables.

    blindman

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you post the base query?


    I gotta test this...but even if it's the same spid...doesn't it create a new instance of a temp table?


    Looking at that it doesn't seem right...but if you call a sproc multiple times...it wont create a new instance of a temp table?

    Gotta test it...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I started to test

    Code:
    USE Northwind
    GO
    
    CREATE PROC mySPROC
    AS 
    SELECT 'Create Occurs', GetDate()
    CREATE TABLE #myTable99 (Col1 int)
    
    DECLARE @x int
    SELECT @x = 1
    WHILE @x < 1000000
    SELECT @x = @x + 1
    SELECT 'DROP Occurs', Getdate()
    DROP TABLE #myTable99
    GO
    and was going to execut in 2 QA windows...and I'm like, duh, 2 spids...

    how would you launch to independeant threads on the same spid...

    but I'm sure you're right...though I'd like to see it...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm not sure I'm right. I'm still looking through Books Online. Some of the statements I see seem to be contradictory.

    blindman

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's a good question though...I use a connection pooling model...and that would be a problem (I think), but I don't utilize temp tables too much (at all) in production....

    And if you schedule 2 different jobs, aren't they separate spids?

    So can't test it that way...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Turns out the issue we were having was not the result of colliding temp tables. It was actually caused be users running a separate procedure that accessed the same source tables.

    We are able to run multiple executions of the stored procedure, but I don't know whether crystal is running them through the same session or not.

    blindman

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Still a good question...

    Are they locking each other? Is this OLTP or LAP?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Sep 2003
    Posts
    522
    i tested asynchronous connection with multiple rs's executing various action queries and sp's, - couldn't get an error to pop up, until i tried to update/delete object that was used in another statement. using this concept i even managed to get a deadlock, from the same connection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •