What''s the magic of ''between'' ?

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  February 2, 2006 at 8:43 am

  #618421

  I'm confused:

  -- Good query

      SELECT      COUNT(*) AS NumOfRec

      FROM          table_name

      WHERE      (dbo.udf_CheckDates(@varStartDate, @varEndDate, dtmStartDate, dtmEndDate) = 1)

      AND  (field_1= @ID_1)

      AND   (field_2= @ID_2)

      AND   (field_3= @ID_3)

  -- bad query

      SELECT      COUNT(*) AS NumOfRec

      FROM          table_name

      WHERE      (dbo.udf_CheckDates(@varStartDate, @varEndDate, dtmStartDate, dtmEndDate) = 1)

  What's the difference ?

  If the most optimal access path to the required data is via an index on one or both of the table's date columns, both are bad queries, from a performance aspect.

   

• Deaf Programmer

  Grasshopper

  Points: 19

  More actions

  February 2, 2006 at 9:28 pm

  #618569

  I used them in queries and the results have shown the difference.

  ---

  SET STATISTICS IO ON

  SET STATISTICS TIME ON

  ---

  Are the STATISTICS useful in measuring the SQL performance ?

  Let me know if I'm wrong.

  Cheers,

• Stewart Joslyn

  SSCertifiable

  Points: 6131

  More actions

  February 3, 2006 at 1:44 am

  #618583

  if Field_1 etc are indexed, you will be filtering the result set so that the udf is called for less rows (look at the query plans). The use of a udf still is going to cause a massive performance hit. Compare a solution without the udf.

• Deaf Programmer

  Grasshopper

  Points: 19

  More actions

  February 3, 2006 at 6:47 pm

  #618816

  I did not mention the query performance in postings.  It was raised with the date issue especially in the where clause.  That's how I response. Unfortunately, someone raised the performance issue on my posting regardless of the date issue.

  I have been doing the indexing for years, as it is a fundamental part in database.

  Thank you.