Filldataset taking long time in

I have the following sp Get_Patient

  Declare @Ctdate varchar = dbo.fnSqlDateFormat_String(Getdate())

 Select Top 100 A.Patientno,A.createdate,Titleid,B.Value Title,Patientname,  
   (Case when dd = 0 and mm = 0 and Yr > 0 then (Cast(yr as varchar) + ' Yr(s)')
        when dd > 0 and mm = 0 and yr = 0 then (CAST(dd as varchar) + ' Day(s)')
        When dd = 0 and mm > 0 and yr = 0 then (CAST(mm as varchar) + ' Month(s)')
        Else '' End) Age,Sex,Phone1,Mobile1,email,A.LoginName,A.Concurrencyid,PAddress,
        (case when (DOB is null) Or (DOB = '') then null else dbo.fnString_SqlDateFormat(DOB)End)DOB,
   from Master_Patient A  
   Left join
   ( Select rowid Id , fieldValue Value from Master_Common  where field = 'Title'
   )B on A.Titleid = B.Id   --where PatientName like '%' + @Searchstring + '%'  and blndisable = 0
   Where A.Createdate  = @Ctdate  

When this sp is executed using filldataset , it executes very fast. but when  i use the following way   .. Where  A.Createdate  =   dbo.fnSqlDateFormat_String(Getdate())
the filldataset is taking very long time to fill the dataset.

But in qry analyzer, both qry run very fast.

Can some expert tell what may be the reason
Who is Participating?
Bob LearnedCommented:
Sometimes you can see gains if you do some of the work on the code side, instead of the database side.  Query analysis would let you know what is taking a long time.

Another optimization is to turn off constraints when filling a DataSet.

How to: Turn Off Constraints While Filling a Dataset
Jacques Bourgeois (James Burger)PresidentCommented:
Query Analyzer enables you to test and possibly increase the performance of a specific query. But it does not always show properly the way things are handled by the client.

But Dataset.Fill intervenes in some way. For instance you can pass parameters to a stored procedure through an EXEC call or through SqlParameter objects. The second one takes more code but is usually faster than calling EXEC, even if under the hood you are using the same stored procedure.

This probably comes into consideration in your tests.
venkataramanaiahsrAuthor Commented:
execution time of   filldataset of storedprocedure  in the following two cases is different

 1.   Declare @Ctdate varchar = dbo.fnSqlDateFormat_String(Getdate())
   .   ...  Where A.Createdate  = @Ctdate       ------Fast
 2.   .. Where  Createdate  =   dbo.fnSqlDateFormat_String(Getdate())   -- Slow

Any specific reason ?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.