Cardinality Estimation - DateTime Parameters Versus Cast?

May 4, 2011

I am trouble shooting poor performance for the query I have posted below. Here is my problem: If I run the exact query below in sql server management studio or using .NET sqlclient the query takes an average of 14 seconds to run.

However, if in .NET I take out this part of the query:
DECLARE @time_diff INT
DECLARE @start_date DATETIME
DECLARE @end_date DATETIME
SET @time_diff = 2
SET @start_date = '05/04/11 00:00:00 AM'
SET @end_date = '5/4/2011 11:59:59 PM'

And use parameters in code like this:
sqlParameter = sqlCmd.Parameters.Add(New SqlParameter("@start_date", System.Data.SqlDbType.DateTime))
sqlParameter.Value = parameters.StartDate
sqlParameter = sqlCmd.Parameters.Add(New SqlParameter("@end_date", System.Data.SqlDbType.DateTime))
sqlParameter.Value = parameters.EndDate

Then the query takes about 2 - 3 minutes to run. Also, I have noticed that it takes about 2 - 3 minutes to run, if i replace the parameter values with string date constants and run in .NET or SSMS

--AND v.call_start_time BETWEEN @start_date AND @end_date
AND v.call_start_time BETWEEN '05/04/11' AND '5/4/2011 11:59:59 PM'

I found this article [URL] on cardinality and the query optimizer and I think it has to do with my issue but it doesn't seem to make sense in my case. According to the article, casts should be executed when determining cardinality. In my case this doesn't appear to be happening. The article also says to use parameters in place of local variables - which isn't working well with .NET parameters either. Regarding using a stored procedure instead: I have a variable number of parameters, I don't know how well it would work out. Even if this is the only option, then i would still like to know exactly what the problem is.

The query:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS IO ON
DECLARE @time_diff INT
DECLARE @start_date DATETIME
DECLARE @end_date DATETIME
SET @time_diff = 2
[Code] .....

View 1 Replies


ADVERTISEMENT

[2005] Today.date Versus Now Versus DateTime.Now?

Jan 16, 2009

just want to know what is the difference between these date values?Once some one told me that its difference is server date and client date but not sure which one he meant.Below is 3 type of date currently I'm using and don't know what is the difference between them.

DateTime.Now is from System.DateTime.Now
Now is from Microsoft.VisualBasic.DateAndTime
Today.date is also from System.DateTime

View 4 Replies

Optional Parameters Versus Method Overloads?

Oct 6, 2009

I was familiar with the optional parameters in vb 6 and it made sense given the capabilities of the language but why the heck does VB.Net support optional parameters when there is method overloading? Which one should I use and is there a difference? If there is a difference when should I use each one?

View 4 Replies

VS 2010 Datetime.tostring Invalid Cast Exception?

Nov 21, 2011

I'm just converting a date to a string but I'm getting this error. It's not listed as one of the normal exceptions to the datetime.tostring method. Googling around, it looks like it's an actual bug where it thinks StartDate is an int--but that's no help to me, as I just need to convert the date.

dim StartDate as nullable(of date)
dim Date1 as string
if not StartDate is nothing then
Date1 = StartDate.tostring("yyyy-mm-dd")
end if

View 11 Replies

Parameters Describe An Un-representable DateTime How To Ignore?

May 20, 2011

I am using ODBC to read data from a pervasive PSQL database and in some scenarios, the date column can contain 00/00/0000 date. I don't really care about invalid dates so is there some way I can convert all of these un-representable dates into Null or some specific date instead of the query failing.

EDIT: The following shows the code I am using and where it is failing:

Private _connODBC As OdbcConnection
Dim dt As New DataTable
_connODBC = New OdbcConnection(txtConnectionString.Text)
_connODBC.Open()

[Code]...

View 4 Replies

Provide Cast Methods For Cast Operator Overloads In C#?

Jun 3, 2009

I read somewhere that older VB .net (pre VB .NET 2005?) couldn't use overloaded operators in a C# class. Is it true for overloaded cast operators?Either way do you guys think it's a disadvantage for a C# class library not to include them and arithmetic methods as actual methods for other languages, like:

ToDouble
ToFloat
FromDouble
Add

[code]....

View 1 Replies

Convert String To Datetime & Calculate The Datetime?

Mar 23, 2011

how to convert string to datetime and calculate date

View 3 Replies

Match A Certain DateTime Value With Current System DateTime

May 7, 2010

I'm trying to match a certain DateTime value with the current system DateTime. I have a timer and a label on a form. Timer interval is set to 1000. When the form loads, the timer starts ticking. As soon as the current DateTime matches the value of the variable, it shows a message in the label. When I'm writing the following code, the values don't match even if the current system DateTime is equal to the variable. Label1 isn't showing 'Times Matched':[code]The default format of 'Now' is the same as I have stored in dtmVar variable. So there's no question of format mismatch. Does that mean 'Now' is not actually a DateTime property? Provided, my O.S. is Windows Vista Ultimate and all date/time settings are set to default.

View 1 Replies

Match A Certain DateTime Value With The Current System DateTime?

May 1, 2010

I'm trying to match a certain DateTime value with the current system DateTime. I have a timer and a label on a form. Timer interval is set to 1000. When the form loads, the timer starts ticking. As soon as the current DateTime matches the value of the variable, it shows a message in the label.

When I'm writing the following code, the values don't match even if the current system DateTime is equal to the variable. Label1 isn't showing 'Times Matched':

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
Dim dtmNow As DateTime

[Code]....

Why is it so? The default format of 'Now' is the same as I have stored in dtmVar variable. So there's no question of format mismatch. Does that mean 'Now' is not actually a DateTime property? Provided, my O.S. is Windows Vista Ultimate and all date/time settings are set to default.

View 1 Replies

Update Datetime Field With Empty Datetime?

Aug 8, 2011

datetime1 and datetime2now two fields are have data ( date )in table recordi want to update only datetime2 is an emptyi am using visual basic 2010 ( visual studio 2010

View 1 Replies

Invalid Cast Exception Was Unhandled - Unable To Cast Object Of Type 'System.String' To Type 'System.Windows.Forms.TextBox'

Feb 9, 2012

Private
Sub cmdExit_Click(ByVal
sender As System.Object,
ByVal e

[code]....

View 3 Replies

C# - Search For Names In The Database That Matches Whole Parameters Or Any Part Of Parameters

May 13, 2011

I'm writing a query to select all records that has any part of parameter. I have one table called Employees. Some people have name like this: John David Clark If the parameter is

[Code]....

I should be able to get result back as long as there's a match in the parameters. If I use Function Contains (q.FirstName & " " & q.LastName).Contains(employeeName), I will not get any result back if employeeName is "John Clark" Function Contains looks only for next words from left to right. It doesn't match a single word at a time. So that's why I used this in the Linq to SQL:

[Code]....

View 2 Replies

Error [07002] The # Binded Parameters < The # Of Parameters Makers

Aug 30, 2010

I am getting error [07002] the # binded parameters < the # of parameters makers, i checked both parameters were perfect even though i am getting this error here is my code

[Code]...

View 1 Replies

C# - Compare Datetime.Now With A Datetime Variable

Dec 27, 2010

I try to compare Datetime.Now with a Datetime variable I set, using the Datetime.CompareTo() method. I use a timer to compare these every second and display the result, but as the current time approaches the time I set, the result changes from 1 to -1, but never 0, which means these two are never equal. I'm suspecting the Datetime structure contains milliseconds?

View 7 Replies

Challenge - DateTime Minus DateTime?

Mar 14, 2011

Assume the following program is started at 1:59:01 AM on 3/13/2011 in the United States. The first time Timer1 ticks what will be the value of the TimeSpan (ts)?

Public Class Form1
Private Sub Timer1_Tick(sender As System.Object, _
e As System.EventArgs) Handles Timer1.Tick

[code].....

View 7 Replies

VS 2010 Add Datetime Into Sql - Datetime - Field

Jan 29, 2011

I'm trying to add the date and time of Today into a Sql-database-field that has the datetype datetime.

[Code]...

the msgbox gives me the following result: 29/01/2011 18:49:21 my sqlException gives me the following error: 102 - Incorrect syntax near '18'. Is it better that i set the datatype to char in my sql database? It's maybe a bit easier? When I do that, can I calculate with these date?

View 4 Replies

C# - Difference With Parameters.Add And Parameters.AddWithValue?

Feb 6, 2012

Basically Commands has Parameters and parameters has functions like Add, AddWithValue, and etc. In all tutorials i've seen, i usually noticed that they are using Add instead of AddWithValue.

[Code]...

since it saves my coding time. So which is better to use? Which is safe to use? Does it improves performance?

View 2 Replies

Get The GET Parameters And POST Parameters In Just One Function?

Aug 6, 2011

is there a way to get the GET parameters and POST parameters in just one function or Collection in ASP.NET? Like using $_REQUEST in PHP? I'm using VB.NET.

View 3 Replies

Convert Iso Datetime To Est Datetime?

Oct 27, 2009

How do I convert ISO datetime to locat EST with vb.net?

I have input time as e.g. "20091027T1639Z" which is ISO GMT.

View 3 Replies

Conversion Of A Character Data Type To A Datetime Data Type Resulted In Out-of-range Datetime Value

Jan 12, 2010

[code] "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated." [code]

View 6 Replies

.net - X=x+1 Versus X +=1?

Apr 30, 2009

I'm under the impression that these two commands result in the same end, namely incrementing X by 1 but that the latter is probably more efficient.If it is correct, why should the latter be more efficient? Shouldn't they both compile to the same IL?

View 17 Replies

IDE Versus EXE Performance In .NET?

Oct 5, 2009

I've developed a .NET application that, among other things, does the following:Uses WebClient to retrieve data from a remote server.
Serves as a socket server to 2 'satellite' applications run on the same machine or on a LAN.When I run the app in the VS IDE, it works great. It quickly gets the data from the remote server and communicates perfectly with the 2 satellites.However, when I build it and run it as an EXE, the response from the remote server is very slow and its communication with the 2 satellite applications become very poor.Is there some important difference between running an app in the IDE and running it as an EXE that could effect it like this?

View 1 Replies

Objective C Versus .net?

Apr 9, 2011

For every member object I have to declare the variable 3 times.One in the declaration. One for property declaration. One to tell the compiler to synthesize all the function. Another one to release all those members from memory.Then I learn about Core Data. I generate diagram, poof the classes are made from me and a lot of things are done behind the screen of that visible classes.

Basically Core Data in Objective C represents some form of relational databases. Of course those databases can have relationship, including many to many relationship. The additional detail of having a third table for many to many relationship is already done.Then, there is indexed property so we can search faster. That's also done.

View 1 Replies

[2008] WPF Versus GDI?

Jan 12, 2009

I have thought of a new project to keep me busy for a while, and it involves a more advanced GUI, specifically I want to display a map and put objects on it or highlight certain areas for example. I've been searching the forum for a little while and ran into two options for more advanced GUI building: WPF and GDI.

View 2 Replies

.net 2003 Versus Mysql?

Jun 21, 2010

i am not able to use connection string?

View 1 Replies

Application.Exit() Versus End?

Dec 8, 2010

Can anyone tell me what the technical difference is between Application.Exit() and End?Why would you choose to use one over the other, for example?

View 5 Replies

Asp.net - SQL Transactions: TSQL Versus .NET?

Nov 19, 2010

I wanted to know what (if any) differences there are to using the SQL Transaction within the application versus written into the stored procedure using TSQL statement. We would need to restructure the stored procs and vb code to get this to work and I'm not sure it would be worth the effort at this time.

Public Sub RetrieveTData(ByVal cID As String, ByVal cnn As SqlConnection) As Boolean
Dim sqlTran As SqlTransaction
cnn.Open()
sqlTran = cnn.BeginTransaction

[code].....

We aren't certain whether the timeout is occurring in DataAlreadyTransferred() or usp_BigNasty_CopyDataFromDB1toDB2 due to how the try/catch is written. We can restructure this code, but will take a week or so to get it to production (no errors occur on test/dev today)

DB1 - permanent storage, used by other applications as well
DB2 - working set, used only by Web App

DataAlreadyTransferred(cID) first checks to see if DB2 has any copies of the records, if DB2 does and those records are clean it deletes them (data could have changed in DB1 and we want the most up-to-date version). If DB2's data is dirty it is left alone and no data is deleted.

usp_BigNasty_CopyDataFromDB1toDB2 copies rows from approximately 20-30 different tables and copies over the perm copies from DB1 into DB2, essentially creating a working set from which the Web App can access

We are aware this is inefficient and are examining ways to improve it, just haven't had time yet... I believe by having the transactions in the app code it's locking many more tables than is really needed. If we move them to the stored procs, less tables will be locked at one time thus improving our chances of removing deadlock conditions/timeout issues we're seeing today. Just not sure on this..

View 2 Replies

C# - Appropriate To Use Generics Versus Inheritance?

Apr 28, 2009

What are the situations and their associated benefits of using Generics over Inheritance and vice-versa, and how should they be best combined?I'm going to try to state the motivation for this question as best I can:I have a class as shown below:

[Code]...

Now suppose I have a repository that takes an InformationReturn argument, that has to strore different fields in a DB depending on the type of Info object T is. Is it better to create different repositories each for the type T is; one repository that uses reflection to determine the type; or is there a better way using inheritance capabilities over/with generics?

View 6 Replies

C# - Division By Zero: Int Versus Float?

Dec 18, 2010

Deviding an int by zero, will throw an exception, but a float won't - at least in Java. Why a float have an additional NaN info, while int haven't?

View 6 Replies

Case Versus If Then Statements.

Apr 26, 2011

i have a program, three radios, and 2 check boxes. the Radios use a constant value and do a multiplication. check one applies a discount. no problem.

the last takes the values of all three radios does a comaparison and displays a label if false, performs a subtraction if true and shows savings.problem>

very convoluted using if statements. and the output is often wrong(i know this is a logic error) i can not find the fault.

would it be easier to use case statments. can and will post code if requested. as it is in if then format currently it is rather long.
Sometimes the answer is so blindingly obvious i fail to see it.

View 4 Replies







Copyrights 2005-15 www.BigResource.com, All rights reserved