asked on
Filldataset taking long time in vb.net
I have the following sp Get_Patient
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
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
Visual Basic.NET
Last Comment
Bob Learned
ASKER
execution time of filldataset of storedprocedure in the following two cases is different
1. Declare @Ctdate varchar = dbo.fnSqlDateFormat_String
. ... Where A.Createdate = @Ctdate ------Fast
2. .. Where Createdate = dbo.fnSqlDateFormat_String
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.