|
A query that used to take milliseconds is now taking 3 seconds to run
The table only has 7000 records and I rebuilt the one index that it uses
I am thinking if I drop/create the table the problem may get fixed, but the table has several dependent views, etc. Will each dependent item need to be created again?
Thanx,
>>>-----> MikeO
|
|
|
|
|
|
In MS SQL Server Management Studio,
1) create a new query
2) paste the query in the query window
3) under the Query menu select the option to "Include Actual Execution Plan"
4) Execute the query.
Check out the execution plan results and you want to avoid "scans", this means that it is reading the table or index sequentially.
Also under the Query menu, choose the "Include Client Statistics" and check those values for anything that may seem out-of-whack.
Pleasse reply to this posting so that I know if this was helpful.
david
|
|
|
|
|
I followed the steps in your reply. Nothing looks to be wrong with the query. It even appears to execute normally in Management Studio. When I execute it in the code on the client though it takes about three seconds.
The client app has been running successfully for about four years. Now it is getting bogged down by two queries. Both do field sums. Any ideas what could change on a server or connection that would slow down a SUM operation?
This is a close approximation of the query that is taking so long:
SELECT SUM(WorkSecs) as WorkTime, SUM(RunSecs) as RunTime
FROM tblData WHERE Machine = 1 and Operator = 1
Thanx,
>>>-----> MikeO
|
|
|
|
|
I need to know how to archive data from particular database when it is growing above 1GB.Also it has to be restored when it was needed.Any Idea
|
|
|
|
|
For most of the time Archiving database can be done by using SQL Jobs, I think at first you need to create a stored procedure which will fetch the data and dump into the archive database and then just call it from SQL Jobs with a given schedule.
|
|
|
|
|
There's no "archiving" in SQL Server, there's only "backup" and "replication".
|
|
|
|
|
You are asking for a strategy to manage your data, you are not going to get a satisfactory answer in a forum post.
Most archiving is done by time stricture, anything that is 2 yo move to the archive database. This type of process requires that you copy (replicate) the data into an identical database and delete it from the production DB. Here you run up against data structure issues, all your chages to production structure needs to be reflected in the archive data.
Queries can be written across both databases, but these are specific to archived results.
Another strategy is data warehousing your data. You need to do some serious research into your business requirements before deciding on an archiving strategy.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi all
i have a table with the PrimaryKey of AutoNumber
so i want to insert a row and retrieve it`s PrimaryKey !!!
by the way my DBMS is MSAccess 2003
can any one help me about this?
|
|
|
|
|
You can't really make the insert query return anything else then the number of affected rows.
You will have to follow your insert statement with a select statement.
Something like:
INSERT INTO Table1 (Field1) VALUES('blablabla')
SELECT MAX(PrimaryKeyField) FROM Table1
My advice is free, and you may get what you paid for.
|
|
|
|
|
No, no no. Really that is about the worse way you can retrieve the inserted key.
|
|
|
|
|
sometimes several people, several apps are operating on the same database table. At the same time.
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).
|
|
|
|
|
Well, they shouldn't. There should be a law against it.
My advice is free, and you may get what you paid for.
|
|
|
|
|
INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])] SELECT [source.]field1[, field2[, ...] FROM tableexpression;
you can first insert and then can retrive scalar value like below
select max(ColumnName1) as Max_RowNumber from table_name;
I guess this will help you.
Reasons are not Important but Results are Important.
http://www.sql4professional.blogspot.com
Swati Tripathi
|
|
|
|
|
Second response without a clue.
|
|
|
|
|
Please ignore both posts above telling you to select the max(field) after your insert. It will be prone to problems if you continue to use this method in a high-usage environment.
MSAccess has a built-in variable for this - @@IDENTITY. For SQL Server it is slightly safet to use the function SCOPE_IDENTITY().
So this is what you're after:
-- Assumes "MyTable" has an Autonumber PK field
INSERT INTO MyTable(Foo)
VALUES('Foo')
SELECT @@IDENTITY AS InsertedKey
|
|
|
|
|
AFAIK it needs some clarification, maybe this:
and that magic variable exists for every connection (similar to "thread-local storage"), so you can access it reliably as long as you continue to use the same connection.
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).
|
|
|
|
|
Very nice. I didn't know about either function. Then again, I haven't had any need for this yet.
J4amieC wrote: MSAccess has a built-in variable for this - @@IDENTITY. For SQL Server it is slightly safet to use the function SCOPE_IDENTITY().
Just in case I will need it in the future, what is the difference / why is it safer to use SCOPE_IDENTITY() with MS SQL ?
My advice is free, and you may get what you paid for.
|
|
|
|
|
Assume two users each having own thread.
User1 inserts record then thread is pre-empted before doing the select.
User2's thread runs and inserts record.
User1's thread restarts and does the select.
At this point @@IDENTITY contains the pk of the last record inserted (i.e.User2's record). SCOPE_IDENTITY returns the last pk for the current thread (i.e. User1's thread).
At least that's how I think it works. 
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
The only valid measurement of code quality: WTFs/minute.
|
|
|
|
|
I don't really know, howver this answer was provided not so long ago:
IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
whatever that means...
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).
|
|
|
|
|
dear friends i used
INSERT INTO MyTable(Foo)
VALUES('Foo')
SELECT @@IDENTITY AS InsertedKey
but it does not work
error is:
"Missing semicolon (;) at end of sql statement"
what should i do?
|
|
|
|
|
I've never done such things, however, given the error message, I would adapt the INSERT part so it fits my needs, then insert a semi-colon between the INSERT part and the SELECT part of that SQL snippet; then take a deep breath, hit ENTER, and watch it doing what I instructed.
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).
|
|
|
|
|
reza assar wrote: error is:
"Missing semicolon (;) at end of sql statement"
what should i do?
Quote Selected Text
try reading the error message, then engaging brain.
Seriously, if you cant debug the error given the erro message then give up programming now.
|
|
|
|
|
The practical issue here is that if you have an insert trigger that creates an identity record the @@Identity will return the ID from the record created by the trigger whereas @Scope_Identity() will return the ID from the record you re inserting in the current procedure.
This is just another reason why triggers are EVIL!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
can you get the identity without an SP, by issuing two SQL commands separated by a semi-colon, like what the OP seemed trying?
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).
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
