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] .....
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
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?
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
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()
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:
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.
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.
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
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:
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
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?
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
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?
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?
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.
[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]
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?
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?
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.
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.
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..
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?
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.
