venkataramanaiahsr
asked on
Filldataset taking long time in vb.net
I have the following sp Get_Patient
Declare @Ctdate varchar = dbo.fnSqlDateFormat_String (Getdate() )
Select Top 100 A.Patientno,A.createdate,T itleid,B.V alue 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,ema il,A.Login Name,A.Con currencyid ,PAddress,
(case when (DOB is null) Or (DOB = '') then null else dbo.fnString_SqlDateFormat (DOB)End)D OB,
dd,mm,yr,A.rowid
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
Declare @Ctdate varchar = dbo.fnSqlDateFormat_String
Select Top 100 A.Patientno,A.createdate,T
(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,ema
(case when (DOB is null) Or (DOB = '') then null else dbo.fnString_SqlDateFormat
dd,mm,yr,A.rowid
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
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
ASKER
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 ?
1. Declare @Ctdate varchar = dbo.fnSqlDateFormat_String
. ... Where A.Createdate = @Ctdate ------Fast
2. .. Where Createdate = dbo.fnSqlDateFormat_String
Any specific reason ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.