|
Going to try asking this a different way and see if anyone can help.
I have two queries:
SELECT * FROM tblData WHERE Machine = 1 and Operator = 1
and
SELECT SUM(WorkSecs) as WorkTime, SUM(RunSecs) as RunTime
FROM tblData WHERE Machine = 1 and Operator = 1
Both of these queries are in a custom application I wrote that has been in operation for about four years. Both queries were taking only milliseconds to return a record. Some time last week the second query started taking about three seconds to return a record.
The owner of the SQL server admits to mucking about with data table, so I have DROPPED the data table and CREATED it again. I have also backed up the database and truncated the transaction logs (though I don't think it would have any effect on this). The second query is still taking about three seconds to execute.
The custom application is written in C++ using ADO and trusted connection. There are no ODBC/DSN connections defined on the client computer.
Can anyone think of anything I can check on the SQL server or the client?
Thanx,
>>>-----> MikeO
modified on Wednesday, May 26, 2010 10:16 AM
|
|
|
|
|
I don't know enough DBA stuff to answer, but the fact that the second query uses aggregating functions would start me looking at things associated with temporary storage. Also maybe verify the response times by executing the SQL on the server. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Not sure where the temporary storage would take place. I have executed the query on the server. There is no noticeable delay. And that is puzzling because both the query sent by the code and the query typed into the Management Studio are executed on the server.
Thanx,
>>>-----> MikeO
|
|
|
|
|
In SQL Server Management studio, under the query menu, select the option to "Include Actual execution Plan" and run your query again.
You want to see lots of seeks and no scans. Scans indicate it is reading the table or index from top to bottom.
give it a shot
|
|
|
|
|
David,
I have done this per your suggestion to an earlier inquiry. There are no scans. It executes as quickly as I would expect in the Management Studio.
Thanx,
>>>-----> MikeO
|
|
|
|
|
Looking at the SQL Profiler on the SQL server I see that each time the second query is called it is followed by an 'Audit Login'. There is always a delay between the query and the 'Audit Login' and apparently my app does not receive a record set until after this is completed. Any ideas what could be causing this?
Thanx,
>>>-----> MikeO
modified on Wednesday, May 26, 2010 8:03 AM
|
|
|
|
|
Further review of the SQL profile shows this:
Main program opens database connection (SPID 55) ...3 sec...
Events processing thread opens database connection (SPID 56) ...3 sec...
[PROCESS NEXT EVENT]
Event processing thread does aggregate query (SPID 56)
Audit login occurs (SPID XX) (XX usually between 57 and 60) ...3 sec...
SET NO_BROWSABLE_TABLE_ON (SPID XX)
sp_prepare 'select * from tblData' (SPID XX)
SET NO_BROWSABLE_TABLE_OFF (SPID XX)
sp_unprepare (SPID XX)
Audit logoff occurs (SPID XX)
Event processing thread does aggregate query on tblData (SPID 56)
Audit login occurs (SPID XX) (XX usually between 57 and 60) ...3 sec...
SET NO_BROWSABLE_TABLE_ON (SPID XX)
sp_prepare 'select * from tblData' (SPID XX)
SET NO_BROWSABLE_TABLE_OFF (SPID XX)
sp_unprepare (SPID XX)
Audit logoff occurs (SPID XX)
Event processing thread does non-aggregate query on tblData (SPID 56)
Event processing thread calls stored procedure (SPID 56)
Event processing thread does non-aggregate query on tblData (SPID 56)
[LOOP: PROCESS NEXT EVENT]
I have never run SQL profiler before. From what I have seen I have to guess that either these 'Audit Logins' were not occurring two weeks ago or opening database connections was not taking as long two weeks ago.
Can anybody run SQL profiler and give me an idea how long opening a connection normally takes?
Thanx,
>>>-----> MikeO
|
|
|
|
|
If anyone is interested, I found a solution to this problem on another forum.
The connection string I am using has the server IP address to avoid any DNS issues. Apparently this is not good enough. I had to place an entry for the server in the hosts table of the client machine. Once this was done the time it takes to connect to the server drops back into the milliseconds range.
Thanx,
>>>-----> MikeO
|
|
|
|
|
Just be careful with the HOSTS table entry; clearly and concisely document what was done and why.
We had a problem last year with not being able to talk to a device because of undocumented HOST table entries.
Tim
|
|
|
|
|
If I drop / create a table in MSSQL 2005 will all of the dependent views still work?
Thanx,
>>>-----> MikeO
|
|
|
|
|
if i get your question right
If you drop a table that is used in a view will it still work? the answer is no you will get binding error
if you then recreate said table? then yes the view will work.
hope that answers your question
Marc Clifton wrote: That has nothing to do with VB. - Oh crap. I just defended VB!
|
|
|
|
|
The question is will I have to drop and create each view again?
Thanx,
>>>-----> MikeO
|
|
|
|
|
i create a test script to try out your question and i didn't have to recreate the view.
but you will have take into account indexes etc.
Marc Clifton wrote: That has nothing to do with VB. - Oh crap. I just defended VB!
|
|
|
|
|
I have created scripts for the indexes and triggers. The list of dependents is quite long though, so I don't want to create too much of a mess.
Thank you for running the test. I should have thought of that.
Thanx,
>>>-----> MikeO
|
|
|
|
|
Do you just want to get rid of all records in that table? Then try a TRUNCATE TABLE query.
|
|
|
|
|
What I am trying to do is find out why a query that used to take 10ms is now taking 3 seconds to return a record set. I was hoping that dropping and then creating the table again might fix whatever had changed. There are only about 7000 records in the table most of the time, so a query should not take that long.
I have followed up on this thread with a new question:
A-tale-of-two-queries[^]
If you have any insight on 'Audit Login' I would appreciate hearing about it.
Thanx,
>>>-----> MikeO
|
|
|
|
|
We have a 3rd party billing app for storing meter readings that uses a version of SQL Server 2003 for saving the data. Over the weekend it reached its limit - 4 or 5 GB - and can no longer accept new data. The vendor claims that it's not possible to free up space by deleting old records, but I find it hard to believe that we can't even execute a query to delete old records manually.
Is this true? Does SS2003 lock everything once it hit its hard-coded limit?
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Roger Wright wrote: The vendor claims that it's not possible to free up space by deleting old records
That is hard to believe; you may not be able to shrink the files, but most of the internal space must become available for reuse within the database.
I'm no DB expert, and have never "filled" a database before; but whatever database you use, this is bound to work:
- stop the application
- create backup 1
- remove garbage and old records no longer needed
- create backup 2
and now either compact database
or, harder but my preferred way:
- delete database
- create new database
- populate from backup 2
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
I think you're right, but unfortunately I can't get access to the DB myself, and the others who have access know nothing about databases except how to follow instructions from a phone support desk. I suspect that it would take me about 20 minutes to solve this if they'd let me in.
I actually found it using Management Studio from my desk, but it won't let me log in... 
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
If you can't do anything about it, it is not your problem.
Whatever becomes impossible due to the situation will result in a consistent "can't do that due to database problems" reply. It will sort itself out eventually. Even management gets in check with reality on occasion.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
If there is no room to create the log of the transaction then you can't execute any transactions that would require logging (with is everything be default). So you would have to do some more drastic things like truncating tables using the with nolog hint to prevent logging. Or you free up space in the transaction log. Shrinking Transaction log[^]
It isn't from Metretek is it? If so, do they still use the ridiculous 60 hourly columns table format for meter reads
|
|
|
|
|
Nope - It's Hunt Technologies/Landis & Gyr Command Center. When it was TurtleWare I could make it jump through hoops on command, but since the upgrade I've been kept away from the details.
It would have been nice of them to include a warning about the space remaining in the software they provided...
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Roger Wright wrote: It would have been nice of them to include a warning about the space remaining in the software they provided...
It's actually called gross incompetence and should be punished - terminally.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I agree, but we already have rather a lot invested in meters, and these systems are very proprietary; we can't change vendors without replacing every electric meter in the system.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Roger Wright wrote: Does SS2003 lock everything once it hit its hard-coded limit?
SQL Server - NO.
SQL Server Express/CE/MSDE - YES (at 4 gig).
Perhaps you need to migrate from SQL Server Express to the full version. 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
