Post reply

Query Tuning in TSQL

  • October 3, 2008 at 9:35 am

    #880543

    Hi Sandy & bcronce,

    I am clear now on my topic. I hope I got my answer from Gail and Sandy.

    Sandy...I Like you..:)

    Thanks a lot Bcronce for nice explanation..

    Luvs,

    Milu.:)

  • October 5, 2008 at 11:08 pm

    #880960

    it's my pleasure, Meely....;)

    Cheers!

    Sandy.

    --

  • October 7, 2008 at 11:43 am

    #881858

    If you have more records (highly transactional ) you should go for temp tables.

    I feel it is good to use temp tables in this scenario since

    1. You have large data.

    2. You may want to create index on tables.

    I suggest you to use temp tables. If you have any constraints using temp tables, let me know.

  • October 8, 2008 at 9:05 am

    #882368

    1) Can anyone give me some justifications for the apparent agreement that table variables should be used for 100 rows??

    2) A minor point on the OP: You should use UNION ALL for cases where either a) you know there won't be duplicates or b) you don't care if there are duplicates. Without the ALL, the engine will SORT/DISTINCT the sets under the covers, leading to lower performance.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • October 8, 2008 at 9:23 am

    #882390

    TheSQLGuru (10/8/2008)

    1) Can anyone give me some justifications for the apparent agreement that table variables should be used for 100 rows??

    I use it as a rough thumb-suck of whether the stats inaccuracy will have too severe an impact. Of course, it's not valid in all cases, sometimes even 10 rows in a table var stuffs a query plan up, sometimes 10000 rows is fine.

    That's why I always follow that up with a 'Test carefully' statement. It's not meant as a hard-and-fast, always applicable, hard limit. I think it came out that way because the OP wanted a very simple statement of which to use when.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 8, 2008 at 11:01 am

    #882460

    Thanks for the clarification!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 6 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply