Case sensitivity when querying SQL Server 2005 with .NET using OleDB

Question

I have a query that I execute from a .NET application to a SQL Server database, and it seems to take quite a long time to complete (5+ minutes). I created a test application in C # to try to see what has been said for so long (the request should return quickly).

Since I restored the query by adding to the elements to see which part takes so long, I ended up restoring the query almost verbatim, where the only difference was the gaps in the original query and the difference in capitalization. This difference returned a result of about 100 milliseconds.

Has anyone seen this before? I am wondering if services are disabled on our server (since a colleague has the same problem) or on our computers.

Thanks in advance for your help.

Example code below (The difference in the first line of the request at the end (fk_source vs. fk _Source):

//Original
    OleDbCommand comm = new OleDbCommand("select min(ctc.serial_no) as MIN_INTERVAL from countstypecode ctc, source s, countstype ct, counts c where ct.value_id=c.value_id and s.c_id=ct.fk_source and " +
      "ct.timeinterval=ctc.typename and ct.timeinterval in ('15min','1h','1day') and c.time_stamp >=  CONVERT(datetime,'01-01-2008',105)  and c.time_stamp < " +
      "CONVERT(datetime,'01-01-2009',105)  and s.c_id = '27038dbb19ed93db011a315297df3b7a'", dbConn);

//Rebuilt
    OleDbCommand comm = new OleDbCommand("select min(ctc.serial_no) as MIN_INTERVAL from countstypecode ctc, source s, countstype ct, counts c where ct.value_id=c.value_id and s.c_id=ct.fk_Source and " +
      "ct.timeinterval=ctc.typename and ct.timeinterval in ('15min','1h','1day') and c.time_stamp >= CONVERT(datetime,'01-01-2008',105) and c.time_stamp < " +
      "CONVERT(datetime,'01-01-2009',105) and s.c_id='27038dbb19ed93db011a315297df3b7a'", dbConn);

Answer 1

I suspect this is a problem with the procedure cache. One of the benefits of stored procedures is that the plan is stored for you, which speeds up the work. Unfortunately, you can get a bad plan in the cache (even when using dynamic queries).

Just for fun, I checked the cache of the procedure, ran an adhoc request, checked again, then executed the same request with a different capitlization, and I was surprised to see that the procedure was counted above.

Try it....

SQL Server Management Studio.

DBCC MemoryStatus

Select Columns... From TABLES.... Where....

dbcc MemoryStatus

Select Columns... From tables.... Where....

dbcc MemoryStatus

, , TotalProcs , ( ).

. , .

Answer 2

SQL Server 2005, SqlCommand OleDbCommand?

Answer 3

, - / ? - .

: , , - - .

Answer 4

-, 100% , ? sql, , .

-, . sql-, -.

Answer 5

, "5+ ", 100 , .

Answer 6

, :

1) Russ, , SQLConnection , , , . , .

2) gbjbaanb, , , , oledb .net(1.1 2.0), , , 5 .

3) Joel Coehoorn, , , , , .

4) Cade Roux. , , , SQL Server .

Answer 7

G Mastros , . G Mastos SQL Server.

, !

, , , .