Post reply

Varchar too small, Text too difficult; Now What?

  • September 8, 2004 at 3:51 pm

    #83968

    I am creating a dynamic sql statement that needs to be exec'd at the end of a stored procedure.  My problem is that the where clause can easily become huge, well over 8000 characters.

    I first thought to use a TEXT data type, but without being able to create or manipulate a @var of type TEXT in my sp code, that type is useless to me.

    My question: how do I build a string in my stored procedure containing my dynamic sql query, given that the query can be longer than 8000 characters?

  • September 8, 2004 at 4:42 pm

    #522205

    You can divide the where clause in to multiple parts, meaning that on a really bad day you can end up with:

    EXEC (@SelectPart1 + @WhereClause1 + @SelectPart2 + @WhereClause1 + @SelectPart2a + @WhereClause2 + @SelectPart3 + @WhereClause3 + @SelectPart4 + @WhereClause4 + @SelectPart5 + @WhereClause5 + @SelectPart6)

     

     

    Ugh



    Everett Wilson
    ewilson10@yahoo.com

  • September 9, 2004 at 8:06 am

    #522319

    If the reason for the large size of the where clause is the number of values being compared, i.e. you have a large "in" comparision, you might consider moving the values to a temporary (or permament table) and doing a join.  This would not only solve the size limitation problem but improve query performance too.

  • September 9, 2004 at 4:26 pm

    #522412

    >>This would not only solve the size limitation problem but improve query performance too. <<

    Thanks for your help, I've tried it both ways (yours and Everett's) and they both work, but interestingly enough, his with the in clause and concatenated exec is faster in execution (25 sec. vs. 1:53).

    Thanks again!

  • September 9, 2004 at 8:04 pm

    #522428

    The reason is probably because my method involved the creation of objects and disk io, his didn't.  I'll file this away for future reference.  Thanks for the reply.

  • September 10, 2004 at 9:15 am

    #522570

    I think Bruce's idea of a table is still worth keeping in the back of your mind.  If I'm ever really, REALLY bored and need a challenge I plan on moving some of the workload to a couple of dynamically updated permanent tables.  Not for optimization's sake (although I'm hopeful) as much as creating something that would be easier for another person to maintain.



    Everett Wilson
    ewilson10@yahoo.com

  • September 10, 2004 at 9:30 am

    #522580

    Yes, Bruce's idea was a good one.  I will keep it in mind for sure.  Interestingly enough, though, it was not necessarily slower because of the the disk io and object creation.  I tried it with a #temp table and also with a table-type @var -- both were slower than the literal in().

    At this point I'm done analyzing it; I've got more programming problems to solve

    Thanks for all your help, both of you.  This site is a great resource for someone like me who is better at front-end programming.

Viewing 7 posts - 1 through 6 (of 6 total)

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