Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Filldataset taking long time in vb.net

Posted on 2014-01-05
4
211 Views
Last Modified: 2014-02-28
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,
        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
0
Comment
Question by:venkataramanaiahsr
4 Comments
 
LVL 40
ID: 39758045
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.
0
 

Author Comment

by:venkataramanaiahsr
ID: 39758380
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 ?
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 39872275
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
http://msdn.microsoft.com/en-us/library/s3bxwk8b.aspx
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question