SQL Server Performance

Want to use ‘CASE’ to perform the operation effici

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by ashish.johri2007, Mar 13, 2007.

  1. ashish.johri2007 New Member

    Hi I need to optimize the query below:
    As the select condition is same in all the cases, I want to use ‘CASE#%92 to perform the operation efficiently.

    SET@i_GLSS_Num= (SELECTGL_Distrib_Evnt_GL_Amt_Type.GL_Distrib_Evnt_GL_Amt_Type_Id
    FROMGL_Distrib_Evnt_GL_Amt_Type
    WHEREGl_Distrib_Evnt_Cd= 'S'
    ANDGl_Amt_Type_Cd= 'S')

    SET@i_GLSCST_Num= (SELECTGL_Distrib_Evnt_GL_Amt_Type.GL_Distrib_Evnt_GL_Amt_Type_Id
    FROMGL_Distrib_Evnt_GL_Amt_Type
    WHEREGl_Distrib_Evnt_Cd= 'S'
    ANDGl_Amt_Type_Cd= 'CST')

    SET@i_GLSD_Num= (SELECTGL_Distrib_Evnt_GL_Amt_Type.GL_Distrib_Evnt_GL_Amt_Type_Id
    FROMGL_Distrib_Evnt_GL_Amt_Type
    WHEREGl_Distrib_Evnt_Cd= 'S'
    ANDGl_Amt_Type_Cd= 'D')


    Ashish Johri
  2. ashish.johri2007 New Member

    Any method is appreciated if this works efficiently. I have listed only 3 instances in my prev. post while in my code I have 87 instances like this.

    Regards,
    Ashish

    Ashish Johri
  3. ashish.johri2007 New Member

    I want to use something like this:

    IF(Gl_Distrib_Evnt_Cd= 'S'
    ANDGl_Amt_Type_Cd= 'S')
    SET@i_GLSS_Num= GL_Distrib_Evnt_GL_Amt_Type_Id
    else if (Gl_Distrib_Evnt_Cd= 'S'
    ANDGl_Amt_Type_Cd= 'CST')
    SET@i_GLSCST_Num= GL_Distrib_Evnt_GL_Amt_Type_Id

    FROM GL_Distrib_Evnt_GL_Amt_Type



    Ashish Johri
  4. Adriaan New Member

    Looks a bit like a non-database developer has translated the ways of moving bits of information from a programming environment, one-on-one, to a stored procedure. That is not the proper way to do it in a database environment.

    Your problem is not to find a more efficient way of moving values into variables, but you'll need to create some set-based logic so you can drop (most of) the variables. Think INSERT and UPDATE queries with proper FROM clauses and lots of JOINs.
  5. ashish.johri2007 New Member

    Hi Adriaan,

    Perhaps I cudn't convery my message. Here you can see in all the queries that the variables are updated with the same Ids. We have to hit the table again and again for 87 times in my code. This is really bad idea to do this.

    Can I use any logic similer to below one?

    DECLARE @i_GLSS_NumINTEGER
    DECLARE @i_GLSCST_NumINTEGER

    IF (1=1)
    SET @i_GLSS_Num = (SELECT GL_Distrib_Evnt_GL_Amt_Type.GL_Distrib_Evnt_GL_Amt_Type_Id
    WHERE Gl_Distrib_Evnt_Cd = 'S'
    AND Gl_Amt_Type_Cd = 'S')
    ELSE SET @i_GLSCST_Num = (SELECT GL_Distrib_Evnt_GL_Amt_Type.GL_Distrib_Evnt_GL_Amt_Type_Id
    WHERE Gl_Distrib_Evnt_Cd = 'S'
    AND Gl_Amt_Type_Cd = 'CST')

    FROM GL_Distrib_Evnt_GL_Amt_Type
    END

    Regards,
    Ashish

    Ashish Johri
  6. Adriaan New Member

    Yes, but what are you doing with those variables in the step after this?
  7. Adriaan New Member

    I overlooked the faulty syntax ... You can use FROM only in a query, in other words: a statement that starts with either SELECT, UPDATE, DELETE or INSERT INTO.

    My main question is why you are moving 80+ values from a table into 80+ variables. This type of thing usually happens when a developer starts writing SQL as if it was a regular programming language.

    SQL is a query language.

    SQL Server uses T-SQL, which is SQL with added programming options. But it's still mainly a query language.
  8. ashish.johri2007 New Member

    Thanks Adriaan.

    The values are moved into variables according to some condition. I am using these variables in the code to update some other table.

    Regards,
    Ashish

    Ashish Johri
  9. Adriaan New Member

    You do know that you can use joins in UPDATE queries? You can add a FROM clause after the column list, and use the complete join syntax exactly like you would in a SELECT query.

    You can use CASE expressions in the column list of the UPDATE query, to evaluate values from the underlying tables, to decide which value will be inserted into the target column.

Share This Page