Using a static table acting as a temp table:

  • Normally building a static table(s) for temporary records may face a conflict among the users that are running ad-hoc queries. So, I was thinking about adding couple of fields that can act as identifiers such as system_user and @@SPID. Thus, each user query will be picking up the right data-set and once done can remove / delete the pulled records from that table(s).

    Do you think this is a good idea? and, do you think there will still be a possible conflicts?

    I have not played around with this concept and appreciate comments and suggestions.

    Cheers,
    John Esraelo

  • Hi,

    System_user and @@spid is enough to act like better concurrency,

    Instead of system_user use the workstation name (host_id()) gives best concurrency in the Static temp tables.

    ARUN SAS

  • I will definitely try that. In fact, after I used the spid I did try with several connections and seemed fine.

    Shortly, I realized that this may not work as you put it "concurrently" because what if the users are coming in via citrix? Also, what if the application running on Citrix and if the connection service account is the same for all users...

    So, I think your suggestion would work better.

    I will give it shot..

    thx

    I will keep you in the loop.

    Cheers,
    John Esraelo

  • John Esraelo (5/14/2009)


    So, I think your suggestion would work better.

    Hi,

    NO, in the Citrix, the workstation name for all users is same i.e. only unique Citrix server name retrieved for all users while you are using the host_id.

    Consider this point before check the concurrency.

    ARUN SAS

  • Ouch, yes, I will have to do some testings..

    I have feeling there will be a unforeseen pitfall along the path that is going to hurt.. a little I hope.

    🙂

    Cheers,
    John Esraelo

Viewing 5 posts - 1 through 4 (of 4 total)

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