Query Performance Strategy
Apr 18, 2008
Hi,
I have just created a logging table that I anticipate to have 10's of millions of rows (maybe 100's of millions eventually).
Basically its a very basic, narrow table, we are using it to log hits on images for a webserver.
My question is that we want to run queries that show how many rows are shown per day etc, however we want to make sure these queries which we are anticipating to be very heavy, do not slow down the system.
I have been recommended to have a seperate database (mirror/replica) for reporting so that the performance of regular activity will not be affected.
I assume this means I would need another server for this other database?
I am thinking there are probably some alternative solutions to this as well. Getting a dedicated server just for these queries really isnt an option.
In order to improvement it is not a problem to make some sacrifices. For example, having the data update every 15 minutes is more than acceptable.
I see certain websites I use employ this strategy of making data update every 15 minutes, but I am unsure what is likely going on behind the scenes. Also the queries are lightening fast when run. I am thinking that they have some sort of table that is populated with some computed data, so its quick to query.
Any thoughts or suggestions to give me some direction, are very much appreciated !
thanks once again,
Mike123
View 12 Replies
ADVERTISEMENT
Jun 23, 2006
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
Jul 18, 2000
Hi all,
Pardon me for asking a question that I know has been asked before. I need to develop a backup strategy for our SQL Server and I am looking for any help that anyone can offer including recommending good books for reading.
Thanks in advance,
Faustina
View 1 Replies
View Related
Oct 18, 2000
In SQL Server 6.5, Is it generally better to dump the
transaction log first, then the database or to dump
the database and then run a dump 'tranlog with truncate
only' option?
Or, is this more a matter of personal choice?
Toni
View 1 Replies
View Related
May 30, 2006
Hi guys.
I am currently developing a system thats stores exchange stats in a db. Since our customers are companies with 20 employees up to 5 000 there a a big difference in the volume of data needed to be stored.
We currently thinking of supplying a SQL Server Express DB to the small customers and suggest a SQL Server to the bigger.
But since I would like to use the same structure for both types of customers I wonder how should i design the storeage.
Since the could be from 500 records a day up to 20 000. There are quite simple recordes with only simple datatypes. about 15 fields with no more than 10 chars each, mostly 2.
Should i separate the data in diffrent tables for a week or a day etc.
Since I am only going to filter data on 1 or 2 fields the data will be easly indexed.
The reports generated will almost always only use 1-3 months of data, but historical reports have to be possible.
My question are ofcourse:
Whats the best solution for me?
Thanks in advance:)
/Johan Wendelstam
Sweden
View 10 Replies
View Related
Jul 23, 2005
I've recently inherited a position where I am responsible for the well-beingof some DBs.2 (much) more important than others.The current recovery model, from what I can tell, is to do a full db/logbackup overnight.This .bak file is then written to tape as well as saved on the disk for 2days.Both these dbs are used fairly extensively 8-5pm and losing data would notbe good.The db sizes are approx 5gb and 3gb.This doesn't seem like the ideal situation to me. Everything I read tellsme... full backup periodically, differential nightly and transaction hourly.Agreed?If so then I have 2 questions:1. Is the best way to do this via a maintenance plan or by scripting andscheduling?2. What, if any, overhead can be expected with regular transaction backupsduring work hours?A bit of a pointer to #1 would be appreciated also.Thanks.
View 4 Replies
View Related
Jun 12, 2006
I have a bit of a problem with regards an indexing strategy. Well,basically there is no indexing strategy on a set of data I have atwork. Now, I didn't create the design as I would have allowed for this.OK, so there is primary key (clustered) indexes (mainly compositekeys), but no other indexes on the tables. As you would expect, theperformance leaves a lot to be desired. A hell of a lot. We haveseveral million rows in a lot of the tables. None of the queries seemto be overly complex so we can work through the applications at a laterstage.We have multiple copies (one per client per country) of the samestructure (I may have considered combining these to allow betterperformance). One specific SP that I need to run takes 40+ hourswithout indexes and 5 hours with some (130) basic indexes to get usstarted on a better design. These 130 indexes are the minimum I suspectwe need and from there, we can start to work out which ones we need.Now the test database (our worst performer) doubles in size to 20Gb,but the performance is much much better (as expected). The originalthinking behind the design was for storage concerns (server spacerecently upgraded) and for performance with bulk inserts.We have a lot of bulk inserts, but I suspect that these are not toobad, and the time taken for indexing these is negligable due to theperformance gains. I strongly suspect that we have a considerableamount of table scans going on, but the problem is that people heredon't understand indexing (yet) or have the time (probably because it'sall taken up using the poorly designed system). That's a whole seperateissue for me to address.So, finally getting round to my questions...Is there any good reference explaining in Layman's terms why you needbasic (or advanced) indexing ? Any links would be appreciated. I needthis to help explain to colleagues why a disk space increase andindexing will be far better than spending thousands on a new box anddoing the same (a common problem I suspect).How can I accurately estimate the amount of time taken to update anindex once data is bulk inserted. I don't want to re-index from scratchas this may take too long. Indexing my database first time round takesabout 1 hour 30 minutes.It's all part of an ongoing bit of digging into the system and re-doingit to make it work properly. I'm sure most of you will have been thereat some point or another.ThanksRyan
View 7 Replies
View Related
May 23, 2007
We are currently doing daily full backup of system & custom databases since database size is small. Is that good idea ? or better option would be weekly full & daily incrementatl ?
Do we need to do any special backup on system databases or transactional logs ?
Please advice
View 30 Replies
View Related
Nov 1, 2007
Hi
I have concern about an sql server. The server has the operating system and sql server installed locally. The databases and transaction log files is stored on SAN. We used to have the database backup and transaction log backups stored locally on the server. We tape the database backup and transaction logs every 24h. If we lose the san and the server then we are stucked with no backup easily accessible. And on tape we loose of 24h data. We decided to put up stand alone server with no connection to the san and dump the backups file on this server. We also put a secondary server sql I case of emergency, to test backups and We are looking at the getting a mirroring or log shipping solution but we are not there yet, next year€™s budget. We still using some old server left from migrating to virtualization.
Then I read the €œPractical Troubleshooting The Database Engine book€? best practice not to avoid net work drive backups. Stuck aging. Back with the backup to local drive and robocopy them? Keep them on the network drive, start using? MIRROR TO in the BACKUP DATABASE? Today we are using the Backup Database Task in the SSIS.
Advice?
Regards
Johan
View 4 Replies
View Related
May 21, 2007
I am running SQL Server 2005 x64 Enterprise under Window Server 2003 x64 Enterprise. After reviewing many posts and suggestions in this forum, I am developing a backup strategy that should include keeping my transaction log file in a manageble size.
Please examine the following proposed backup schedule and let me know if this is considered a sound plan. The scripts below will write to disk and each night and then be backed up to tape.
*** TASK 1 ***
Backup transaction log
/* This script backs up the DSS database transaction log to disk, overwriting any
previous backup
*/
BACKUP LOG [DSS]
TO DISK = N'g:mssqlackuplogdss_log.bak'
WITH
INIT
, NAME = N'DSS-Transaction Log Backup'
GO
*** TASK 2 ***
/* This script shrinks the DSS database transaction log file
*/
BACKUP LOG [DSS] with truncate_only
dbcc shrinkfile(DSS_log)
**** TASK 3 ****
/* This script backs up the DSS database to disk, overwriting any
previous backup
*/
BACKUP DATABASE [DSS]
TO DISK = N'g:mssqlackupdatabaseDSS.bak'
WITH DESCRIPTION = N'DSS Full Database Backup'
, INIT
, NAME = N'DSS - Full Database Backup'
GO
/* Backup validation to ensure the file is valid before storing it */
RESTORE VERIFYONLY
FROM DISK = N'g:mssqlackupdatabaseDSS.bak'
WITH FILE = 1
GO
*** TASK 4 ***
Update statistics on the DSS database
View 3 Replies
View Related
Apr 23, 2008
Currently I have 1 server running MSSQL 2005 Standard. There is no redundancy in my current solution. I'm working on a project that involves a separate installation. I have 3 goals:
1) Provide redundancy for the new installation
2) Provide high availability for the new installation
3) Provide reduncancy for the current installation
Here's what I'm thinking about doing:
1) Purchase 2 servers and a Dell MD3000i ISCSI storage box. Cluster the servers and install SQL Server on the cluster.
2) Install SQL Server on an existing box (single CPU license) for backup purposes. Enable log shipping from the new cluster to the backup server.
3) Enable log shipping from the existing installation to the backup box.
I've also though about offloading some of the static pricing operations to the backup installation. This would free up my existing installation to deal with the changing data.
A couple of questions:
-How does this strategy sound? Are there any configuration problems with it?
-Can I legally use a development edition for the backup installation (if all I do is log shipping to it)?
Any other comments are welcome.
Thanks!
Brian
View 14 Replies
View Related
Jun 15, 2007
This may seem like a silly question, but has anyone ever heard of a DBA or an Engineer deciding to not back up databases inside EM, and only relying on the RAID or third party software for redundancy?
SBS 2003 R2
SQL 2000
Veritas 8.6 open file agent, SQL agent, Exchange agent
Thanks for any input,
Rich
View 3 Replies
View Related
Mar 2, 2007
Hi,
In my current organisation they are using SQL Server.
They are using TSM (Tivoli Storage Manager) to back up the server on a nightly base.
However I feel that this is not the correct way. Suppose I need some data back, I call technical support and they would restore the server. If another user made some changes to another database that day, he would lose his changes.
Of course they could restore a file but I am not sure if this is correctly. What will happen to the transaction log for example ?
My idea is that they should backup the database using the normal sql backup commando, dump the data to a folder and backup that folder.
Any suggestions please ?
Constantijn Enders
View 5 Replies
View Related
Apr 22, 2007
I have been developing a genealogy application using a SQL Server 2000 database and ASP .NET 2.0. In this application a process, Ged.Parse, converts data from the GEDCOM standard format (a heirachical file format that looks as if it was designed for 80-column cards) into my SQL Server database.
As we started to load reasonable quantities of data into the system we found that the on-line response became abysmal. This problem was fixed by defining a number of secondary indexes (response times dropped to under a second, from previously exceeding 2 minutes and often timing out). Unfortunately however the processing time of Ged.Parse then tripled, and it may now take up to an hour to process a GEDCOM. I believe that this is a byproduct of defining several indexes that are not needed by Ged.Parse itself, but which are of course maintained as Ged.Parse inserts new records into the database.
I am wondering what my best strategy is, apart from putting Ged.Parse into a background task and just letting it trickle away. (I will probably do this anyway). What I'd like to be able to do is to have Ged.Parse load records without creating the secondary indexes, and then create the indexes for the newly-added records as a penultimate step just before it makes them available for general use. Of course there is no way that you can do this: records in a table are either indexed or they are not.
Proposed change: recode Ged.Parse to load data into temporary tables, say NewPeople, NewFacts, etc., with these tables having only the indexes required by Ged.Parse. Then, as the last process in Ged.Parse run a SQL procedure with code like: - Insert into People Select * From NewPeople Delete from NewPeople etc
This is a reasonable amount of programming, so before I make this change could somebody tell me: will this be significantly faster overall, or is this likely to make little or no improvement compared to the present process in which Ged.Parse loads data directly into People, Facts, etc? Two facts that may influence the answer. First, all record relationships are through GUIDs, so records in NewPeople, NewFacts, etc would already have their final key values. Second: although Ged.Parse needs to form relationships between records, these relationships are only within the new records (created from the same GEDCOM), and Ged.Parse does not need to relate any of these new records to earlier records.
Thank you,
Robert Barnes.
View 2 Replies
View Related
Nov 27, 2000
Hi!
It would be very nice if some people out there, using
merge replication can tell me their strategy to get
started with a new subscriber. Is it a good way to copy
a publisher backup to the new subscriber and restore it
there and say that schema and data is already here when
creating the subscription?
I experienced some troubles when I tried to add a new
subscriber and used the initial snapshot transfer to
get the db to the new sub. (no defaults are transferred...???)
Or is it even better to use DTS?
I'd also be very grateful forinformations about RESCUE
STRATEGIES in case of a major database problem of replicated
databases!
Hoping for some answers. TIA & Best Regards
Gert
View 1 Replies
View Related
May 4, 1999
Hi All,
Can any one help me with this..
I've a critical application that can't be stopped for a second.
I'd like to have an implementation that uses 2 sql 6.5 servers one as standby and
which is ready and up to date to take place and run instead of the master
server when it's down and when the master is back to work it's updated with the
data entered to the standby.
This process must be automatically to maximum extent.
Thanks
Mohamed
View 1 Replies
View Related
Oct 22, 2004
I am using SQL Server 2000 and trying to create a disaster recovery strategy that would run nightly and backup the database or at least the changes and would ftp these to a secure ftp site. For smaller database it is easy, I just take a full backup, zip up the file and ftp it to the secure backup site. This strategy does not work so well when the zipped up database is still close to 3GB. I have a pretty big window for doing everything but 3GB is just too much to ftp overnight. The recovery model is simple so the only other option seemed to be do a full backup once a month and take differentials nightly. The problem is I am offsight and the client may need to take a full backup during the day and my nightly differential would get screwed up.
There is a fairly low volume of transactions so the idea of just doing nightly backups on the data that has changed is the obvious choice but differentials don't seem to fit. Any ideas?
Thanks,
TH
View 2 Replies
View Related
Oct 20, 2006
Hi Folks,
The Need : Refresh a part of local database daily from remote server.
Assumption : All updates in remote are updated in local db as well.
Need inputs on the type of strategy
1) Take full backup of remote, refresh on local
( Downside for us is Network and disk space )
2) DTS ( refresh only the objects required )
Looks good to us but does it take care of my assumption ?? Your suggestions welcome .. I may be wrong
3) replication ( Dont want it implement on the already complicated sceanrio ... so I'll pass)
4) Standy databases ( ??? Any help on this)
5) Any other
Thanks so much,
Warm Regards,
Ranjit.
--------------------------------------------------------------
The best moments of my life are often things I get paid for
View 3 Replies
View Related
Jun 14, 2007
My maintenance plans are starting to acting weird. I'm building a custom script to manage the database backups on my server, but curious if anybody has some sample work that will allow me to avoid re-inventing the wheel.
A couple of primary constraints:
I want to do a full backup daily (and only retain 1 day of full backups)
Transaction Log backups every 20 minutes
I'd like to loop through the databases on the server automatically to make this a little more flexible.
You have anything you'd like to share? Or, bits of knowledge worth sharing?
Please advise,
alex8675
View 3 Replies
View Related
Oct 7, 2005
My SQL acumen stems from just a couple courses, and everything since fromthe trenches. Fun + angst over time.I'm needing some advice on joins. Though I understand the basics, I'mhaving problems abstracting from instances where it's easy to think aboutdiscrete key values (. . . and studentid = 1234) to entire sets of users,with the joins doing their work.For example, currently I'm going nuts trying to return dates for whichattendance has not been taken for students, but should have been. Studentshave active and inactive periods of enrollment in our schools, so we have ahistory table of when they were active and inactive -- as well as two moretables that layer other bounds on eligible dates (what range of dates fallwithin a given school's term? What of holidays and staff institute days?).I also have a populated calendar table, and a table where students areidentified. Finally, there's a site history table which is a REAL pain inthe butt for me to think about.CREATE TABLE Student (StudentID int IDENTITY(1,1) NOT NULL,CurrentStatus varchar(2) NOT NULL)CREATE TABLE Calendar (Dateid int NOT NULL,Date datetime NULL,Workdaybit NULL )CREATE TABLE DailyAttendance (StudentID int NOT NULL,AttendanceDate datetime NOT NULL,SiteID varchar(6) NOT NULL,Attend_Status varchar(2) NOT NULL)(the last field is, e.g., present or absent)CREATE TABLE StudentActivityHistory (StudentID int NOT NULL,StatusStartDate datetime NOT NULL,StatusEndDate datetime NULL,Activity_Statusvarchar(2) NULL,StudentStatusHistoryIDint IDENTITY(1,1) NOT NULL)(the activity_status is either A or I; the important records in this tableare the 'A' records. A student's most recent status record always has anend date of '12/31/9999 12:00:00 AM', whether that's an A or I record. Nodates not between start/end dates of students' A records would needattendance taken. students may have many periods of activity -- A records-- as well as many inactive periods.)CREATE TABLE SiteTerms (SiteID varchar(6) NOT NULL,Term varchar(3) NOT NULL,StartOfTerm datetime NOT NULL,Quarter varchar(2) NOT NULL,SchoolYear varchar(9) NOT NULL,EndOfTerm datetime NOT NULL)(different schools vary their term start and end dates. No dates notbetween term start and end dates would need attendance taken by studentsassigned to and active in that school during that period.)CREATE TABLE SiteExceptionDays (SiteID varchar(6) NOT NULL,SchoolDayStartTimedatetime NOT NULL,SchoolDayEndTime datetime NOT NULL,SchoolDayType varchar(2) NOT NULL)(there are two kinds of days -- partial attendance, and no attendance. Inshort, if the type of day is "N" no attendance needs to be taken forstudents assigned to that school and active on that day)CREATE TABLE StudentSiteHistory (StudentID int NOT NULL,SiteStartDate datetime NOT NULL,SiteID varchar(6) NOT NULL,SiteEndDate datetime NULL,StudentSiteHistoryIDint IDENTITY(1,1) NOT NULL)(Pain. The attendance table tells which site a student was assigned whenattendance was taken. To find which school a student was a assigned to ondays attendance was NOT taken, this table's implicated 'cause it's the onlyway of connecting everything else together)Dangitall, I know this can be done but I've beat my head against the wall.Due diligence has gotten me a headache and a hankerin' for whiskey, and I'mnot much of a drinker. Is there anyone in the group for whom this kind ofthing is a no-brainer? I'd just as soon get some tips on how to approachthis kind of thing, and figure it out myself with some guidance.Any takers? Gotta run, dang I'm late for something.TIA--Scott
View 4 Replies
View Related
Apr 3, 2008
hello all , I need help to implement this package that will update/add/delete a row from one table to another.
I€™m trying to create this package:
Insert an entire row using SSIS from one table to another based on condition:
A for add, D for delete, C for change
MASStable
Column0
FirstName
Lastname
,MiddleName
A
John
daniels
D
sarah
jones
G
C
yann
coleman
J
daniel
lope
If column 0 = €˜A€™ in masstable add entire row to Deathtable
If column 0 = €˜D€™ in masstable Delete row from Deathtable where Masstable.LastName = Deathtable.LastName
If column 0 = €˜C€™ in masstable Update row (some columns) where Masstable.LastName = Deathtable.LastName
If column 0 = €˜ €˜ no tasks..
This is my mastertable
Deathtable
Column0
FirstName
Lastname
,MiddleName
Juan
danring
sarah
jones
G
yann
coleman
daniel
lope
Do you have a hint.?
View 4 Replies
View Related
May 7, 2007
Hi all
My company is going to open new working centers on different locations of my region. One of the problems we are suffering is that at some locations, the network communications infrastructure is very very very poor. So, in that locations we work with low bandwidths, and connections usually break down.
Because of this, we are thinking in distributing our database. I have been doing some tests on replication, reading the docs, etc... But I am still not sure which replication strategy should we use, and how to organize our database tables to allow replication work properly.
Our offices are going to share some data (a product catalogue, for example) which could be updated an queried from any of the offices. But there is also data which is not going to be shared, as product stocks for each location.
We were thinking on using transactional peer-to-peer replication. But now we are having serious doubts about this since in a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1525643&SiteID=1) we were told we can't store non-shared data (stock) in the same table as the shared data (products) due to how database behaves when a publication is restarted. We know it would not be usual to republish, but we were thinking on crash recovery.
It would be fantastic if somebody could help us to decide what should we do to organize our database to allow the use of a proper replication scheme.
Thanks in advance,
Rubén D. M.
View 4 Replies
View Related
Sep 27, 2006
Hello.
I have only ever been required to take a full back up of my main prod database every night.
Now the times they are-a changing , and it is now required to be able to restore the database up to the last hour.
I've never really done much with tran log / differential backups so I'm asking for some advice as to what should be the best strategy. We are not a 24/7 shop we work from 6:30 am to 6:30 pm every day, so I thought:
Full backup @ 7pm
Backup tran log every hour after that starting @ 7am (as there are no changes overnight)
How does that sound? also when the tran log is backed up, is it truncated? Or do I need to shrink it? Basically I need to know what to do so it doesn't get too big!
Thanks
View 1 Replies
View Related
May 19, 2004
Hi..
Is there any specific place where I can find which SQL Query is more efficient?.
Is INNER JOIN is faster or Select ... Where ID in (SELECT ...) is faster?
I have two tables:
1.FLEET (The number of rows is not so much)
Attributes :
Company_Id (PK)
Fleet_Id (PK)
Fleet_Name
Fleet_Description
2.USER_PRIVILEGE (The number of rows can reach up to 3 times the number of row in fleet table)
Attributes :
Company_Id (PK)
Fleet_Id (PK)
User_Id (PK)
Privilege_Id(PK)
Comment
Category
I want to select Fleet_Id and Fleet_Name from fleet table
Where the current user has privilege_id=1
I have two possible select statement :
1.Option 1
SELECT Fleet_Name, Fleet_Id
FROM FLEET
WHERE (Company_Id = 2) AND (Fleet_Id IN
(SELECT fleet_id
FROM user_privilege
WHERE user_id = 11 AND company_id = 2 AND privilege_id = 1))
ORDER BY Fleet_Name
2.Option 2
SELECT F.Fleet_Name, F.Fleet_Id
FROM USER_PRIVILEGE U INNER JOIN
FLEET F ON U.Fleet_Id = F.Fleet_Id
WHERE (F.Company_Id = 2) AND (U.Privilege_Id = 1) AND (U.User_Id = 11)
ORDER BY F.Fleet_Name
Actually which one is faster. Is SQL Statement with INNER JOIN (Option 2) can be executed faster than the one with double Select Statement(Option 1)?
Any suggestion is welcomed.
Thank you very much.
View 1 Replies
View Related
Dec 12, 2005
Hi:
I have the following query, can somebody help me?
SELECT s.Id, s.NameFROM Switch s INNER JOIN SwitchTelephoneRange r ON s.Id = r.IdWHERE '1526858' BETWEEN FromTelephone AND ToTelephone
Where the '1526858' is a phone number.
My problem is, I want to run the above query for each record in :
select Telephone from PhoneDirectory
So, each telephone number in the second phone, would macth the ' ' in the first query.
How can I do so? Do I need a loop? a cursor? Can you help please?
Thanks
View 5 Replies
View Related
Nov 14, 2003
Hi,
I want to know query execution time as output. I want execution time only. this is for tuning purpose... Time displayed in the status bar is not helpful for me.
thanks.
View 1 Replies
View Related
Mar 25, 1999
Hi there,
I'm having a big performance problems with sql query. What i have done is changes physical layout (rearanged the columns) in one of the tables in the database. I used bcp to get the data out and then in. There are about quarter million rows in the this table. I have created the same indexes but know the same query takes a long time to execute. I have noticed that the showplan is somehow different than it used to be. This query uses the table i have changed and another one that i haven't touched. I have updated the stats to no help.
Here are the show plans.
this one is slow
STEP 1
The type of query is INSERT
The update mode is direct
Worktable created for ORDER BY
FROM TABLE
SW_PERSON
Nested iteration
Index : swiPERSON10
FROM TABLE
SW_CASE
Nested iteration
Table Scan
TO TABLE
Worktable 1
STEP 2
The type of query is SELECT
This step involves sorting
FROM TABLE
Worktable 1
Using GETSORTED Table Scan
this one used to be fast
STEP 1
The type of query is INSERT
The update mode is direct
Worktable created for ORDER BY
FROM TABLE
SW_CASE
Nested iteration
Table Scan
FROM TABLE
SW_PERSON
Nested iteration
Index : PK_SW_PERSON_1__27
TO TABLE
Worktable 1
STEP 2
The type of query is SELECT
This step involves sorting
FROM TABLE
Worktable 1
Using GETSORTED Table Scan
I think the problem is with the fact that the fist one doesn't use the PK
key which is the one the links both tables. My question is how to force the query to use this index.
PS.
One thing i haven't done is to recreate indexes on the other table. But i don't think that would have made a differnece.
Thanks
View 4 Replies
View Related
Oct 16, 2006
I have the following functions in my database
Code:
CREATE FUNCTION dbo.fnProductsRetrieveBySupplierID
(
@SupplierID int
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM Products
WHERE SupplierID = @SupplierID
)
CREATE FUNCTION dbo.fnSuppliersRetrieveBySupplierID
(
@SupplierID int
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM Suppliers
WHERE SupplierID = @SupplierID
)
I have been testing the performance of the following SQL statements:
Code:
1.
SELECT *
FROM Products INNER JOIN
Suppliers ON (Products.SupplierID = Suppliers.SupplierID)
WHERE Products.SupplierID = 3
2.
SELECT *
FROM dbo.fnProductsRetrieveBySupplierID (3),
dbo.fnSuppliersRetrieveBySupplierID (3)
I have built a loop to execute each statement multiple times and then compare the execution times. Although both queries produce the same result, the 2nd one (which uses the functions) is about twice as slow, does anyone know why?
thanks
View 1 Replies
View Related
May 21, 2006
Hi All,
I am joining three tables each table has got about 1.5 million rows,selecting data from these three tables and inserting into a table,to avoid transaction log issues I am running the query in a batch of size 50,000 rows,it is taking about 5hrs to insert all the 1.5 millions rows.
All the columns in the where clause have proper indexes,I ran show plan for the query and it is using indexes properly and not doing any table scan.I updated the statistics for all the indexes also.
query looks some thing like this.
insert into table d (col1,col2,col3,.............. )
values (a.col1,b.col2,c.col3 .....................)
from a,b,c
where a.id = b.id
and a.id = c.id
and a.id in between @minid and @currid
The @minid starts from 1 and @currid starts from 50000 ,I am running this in a loop, in next iteration @minid will become 50001 and currid 100,000 and so on.
Any idea why it is taking that much time?.
Thanks in advance,
bsr.
View 7 Replies
View Related
Jan 22, 2004
I have two tables.
Employee
EmployeeCode int Primary Key
Employee_Stock
EmployeeCode int
StockCode varchar(10)
Primay key on (Employeecode, StockCode)..
There is no foreign key relation between these 2 tables.
Now my question is which query give more performance. and why?
1. Select * from
Employee INNER JOIN Employee_Stock on Employee.Employeecode = Employee_Stock.EmployeeCode
2. Create a foreign Key between Employee and Employee_Stock for EmployeeCode. and run the same query.
Actually we forgot to put the foreign key relationship between these 2 tables and we have lot of queries joining them..
Now if we add foreignkey, is it going to improve the performance or not?
Thanks
Ramesh
View 1 Replies
View Related
Apr 8, 2008
I wrote a query and I use cursor in the query. When I run the query on dev box it takes 3 mins.
I moved the query to EPM database box and it takes forever to run.
Usually EPM database query performance is much better. How do I start debugging the poor performance?
How can I check if the query is creating any table locks?
Purpose of query: I get all the Companies (20000) and loop thru each company in the cursor and do calculations.
Thanks...
View 13 Replies
View Related
Jun 27, 2007
Hi all
I want to check the query performance
How I should starT
I mean what is the plan to be followed
Thanks In Advance
Malathi Rao
View 3 Replies
View Related
Mar 9, 2008
I have a query like below and it takes a aouple of seconds to run
select a.Registration_Key, ag.Agreement_Type_Name,p.ServiceProvider from dbo.Assessment a
INNER JOIN (
select distinct Registration_Key, p.ServiceProvider, max(CSDS_Object_Key) as [Sequence]
from dbo.Assessment a
INNER JOIN dbo.CD_Provider_Xref p
ON a.Provider_CD = p.Provider_CD
where Creation_DT >= '07/01/2007'
and Reason_CD = 1
group by Registration_Key, p.ServiceProvider
) as s1
ON a.CSDS_Object_Key = s1.Sequence
INNER JOIN dbo.CD_Provider_XREF p
ON a.Provider_CD = p.Provider_CD
INNER JOIN dbo.CD_Agreement_Type ag
ON ag.Agreement_Type_CD = a.Agreement_Type_CD
LEFT OUTER JOIN (
select distinct Registration_Key, p.ServiceProvider
, 1 as served
from dbo.Encounters e
INNER JOIN dbo.CD_Provider_Xref p
ON e.Provider_CD = p.Provider_CD
where Encounter_Begin_DT between '08/01/2007' and '08/31/2007'
and Procedure_CD is not null
and Encounter_Units > 0
) as s2
ON a.Registration_Key = s2.Registration_Key
and p.ServiceProvider = s2.ServiceProvider
group by a.Registration_Key, ag.Agreement_Type_Name, p.ServiceProvider
However, if i add a served field( stamped with 1) it takes forever to run.. All of join columns have indexes.. cluster and non-clustered.. and i don;t see any index fregmentaitons...
select a.Registration_Key, ag.Agreement_Type_Name,p.ServiceProvider, served from dbo.Assessment a
INNER JOIN (
select distinct Registration_Key, p.ServiceProvider, max(CSDS_Object_Key) as [Sequence]
from dbo.Assessment a
INNER JOIN dbo.CD_Provider_Xref p
ON a.Provider_CD = p.Provider_CD
where Creation_DT >= '07/01/2007'
and Reason_CD = 1
group by Registration_Key, p.ServiceProvider
) as s1
ON a.CSDS_Object_Key = s1.Sequence
INNER JOIN dbo.CD_Provider_XREF p
ON a.Provider_CD = p.Provider_CD
INNER JOIN dbo.CD_Agreement_Type ag
ON ag.Agreement_Type_CD = a.Agreement_Type_CD
LEFT OUTER JOIN (
select distinct Registration_Key, p.ServiceProvider
, 1 as served
from dbo.Encounters e
INNER JOIN dbo.CD_Provider_Xref p
ON e.Provider_CD = p.Provider_CD
where Encounter_Begin_DT between '08/01/2007' and '08/31/2007'
and Procedure_CD is not null
and Encounter_Units > 0
) as s2
ON a.Registration_Key = s2.Registration_Key
and p.ServiceProvider = s2.ServiceProvider
group by a.Registration_Key, ag.Agreement_Type_Name, p.ServiceProvider, served
View 3 Replies
View Related
