MS SQL Server :: De-Duplication Performance Issue

How To Avoid PrimaryKey Duplication?
Hi, I having a problem with my query...

I want to copy data from 4 different database to 1 database... but if the destination database have already the same Primary Key the copying stops/terminated and not copying others that is not yet in the destination...

I don't have knowledge in T-SQL like IF...ELSE
my database is SQL Server 2000 but i'm using SQL 2005 Express Management for the query...

What i'm doing is like this:

Use osa (Destination Database)
Go

DELETE FROM tblFaculty (*I'll delete first the datas to avoid duplication)

INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM cislucena.dbo.tMasFaculty)

INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM amapn.dbo.tMasFaculty)

INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM abe.dbo.tMasFaculty)

INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM aclc.dbo.tMasFaculty)

My problem is if the facultyID (PrimaryKey) which i'm copying is already on the destination which is osa, the copying stops/terminated regardless whether there is more to copy. On the 4 source database, there might data that other database also has. That's why the copying is terminated. All i want to do is to check first each FacultyID if it is already on the destination before copying it to avoid error or duplication of Primary Key so it won't terminate the copying.

How is this possible sir? Anyone care to help? Thanks in advance! More Power!

Best Regards

View Replies !

SQL Performance Issue
I have a table which is similar to this
VehicleRef, Manufacturer, Model, Derivitive, Term, MilesPA, CH
1000, Audi, A3, 1.6 SE, 12, 10000, 104.991000, Audi, A3, 1.6 SE, 12, 20000, 102.991000, Audi, A3, 1.6 SE, 12, 30000, 102.991000, Audi, A3, 1.6 SE, 24, 10000, 102.991000, Audi, A3, 1.6 SE, 24, 20000, 102.991000, Audi, A3, 1.6 SE, 24, 30000, 102.991000, Audi, A3, 1.6 SE, 36, 10000, 102.991000, Audi, A3, 1.6 SE, 36, 20000, 102.991000, Audi, A3, 1.6 SE, 36, 30000, 102.99
The above all relates to 1 vehicle and there are about 500 vehicles, the primary key is vehicle ref.
For my page of results I want to display each unique vehicle and the lowest CH price for that vehicle (and also the term and milespa relating to the lowest price)
First I fill a datatable with the following query (lowerprice and upperprice are variables)
SELECT DISTINCT vehicleref,manufacturer,model,derivative FROM tblFigures WHERE ch > lowerprice AND ch < upperprice
This works fine and the query executes in under a second.
The problem comes when I try and loop through the datatable and add the additional info using a different query.
SELECT TOP 1 ch,term,milespa FROM tblFigures WHERE vehicleID = 1000 and ch > lowerprice and ch < upperprice ORDER BY ch ASC
When I add the second part it takes over a minute to get the results.
Is there anyway I can optimise this, either using indexes or more concise select statements perhaps, any help appreciated.
Thanks

View Replies !

Please Help The Performance Issue.
Hi,I have wrote the following sql sentence.Do you have comments to improvethe performance.I have created all the indexed. But it's still veryslow.ThanksThe primary key is proj_ID and Task_UID.SELECT PR.PROJ_NAME AS PRName, PR.PROJ_ID As PRProjID, PR.TASK_UID AsPRTaskUID, 'Dev' AS GroupType,Feat.PROJ_ID As FeatProjID, Feat.TASK_UID As FeatTaskUID, Feat.FeatureID ASFeatureID,dbo.CreateFIDSort(Feat.FeatureID) as FIDSort, Feat.FeatureName ASFeatureName, Feat.Entity AS Entity,Feat.CmtStatus AS CmtStatus, SE.AttrName AS SE, SE.ValueVariant AS SEHRID,NetworkElement.ValueVariant As NetworkElement,Pers.FirstName as FirstName, Pers.MiddleInitial as Middle, Pers.LastName asLastName,CASE WHEN Priority.ValueVariant Is Null Then 9999 When Priority.ValueVariant= 0 Then 9999Else Priority.ValueVariant END AS Priority, 'Doc' AS DocType, Doc.PROJ_ID ASDocProjID, Doc.TASK_UID AS DocTaskUID,Doc.ID_Code AS DocNum, Doc.Entity As DocEnt, DocName.Task_Name AS DocName,DocNotes.Task_RTF_Notes AS DocNotes,DocDetails.Proj_ID AS DateProjID, DocDetails.Task_UID AS DateTaskUID,DocDetails.Task_TypeAS DetailTaskType, DocDetDates.TASK_FINISH_DATE AS CWVFinish,DocDetDates.TASK_BASE_FINISHAS BasFinish, DocDetDates.TASK_ACT_FINISH AS ActFinish, DocDetDur.TASK_DURAS TotalDur, DocDetDur.TASK_REM_DUR AS RemDur,DocDetDates.TASK_ACT_START AS ActStart, Sortnum.DocNum As DocSortNum,PR.PROJ_NAME AS PR1Name, 'Disp' As FeatDispFROM CPR_enum_ReltoProj Rel WITH (nolock)INNER JOIN CPR_PATH ReltoFeat with (nolock) ON Rel.PROJ_ID =ReltoFeat.PRED_PROJ_IDAND Rel.TASK_UID = ReltoFeat.PRED_Task_UID AND Rel.PROJ_NAME LIKE 'R26.0'AND ReltoFeat.EDGE_ID = 1INNER JOIN CPR_TASK_FeatCmtStat Feat WITH (nolock) ON ReltoFeat.SUCC_PROJ_ID= Feat.PROJ_IDAND ReltoFeat.SUCC_Task_UID = Feat.TASK_UID AND Feat.CmtStatus <> 'Concept'AND Feat.CmtStatus <> 'Identified'AND Feat.CmtStatus Is Not NullLEFT JOIN ( CPR_PATH FeattoPR WITH (nolock)INNER JOIN CPR_ENUM_PRtoProj PR WITH (nolock)ON FeattoPR.PRED_PROJ_ID = PR.PROJ_ID AND FeattoPR.PRED_TASK_UID =PR.TASK_UID )ON Feat.PROJ_ID = FeattoPR.SUCC_PROJ_ID AND Feat.TASK_UID =FeattoPR.SUCC_TASK_UID AND FeattoPR.EDGE_ID = 1LEFT JOIN CPR_ContainerAttr SE WITH (nolock) ON Feat.PROJ_ID = SE.PROJ_IDAND Feat.TASK_UID = SE.TASK_UIDAND SE.AttrName in ('SEM','SEA')LEFT JOIN CPR_Person Pers WITH (nolock) ON Pers.HRID = SE.ValueVariantLEFT JOIN CPR_ContainerAttr NetworkElement WITH (nolock) ON Feat.PROJ_ID =NetworkElement.PROJ_IDAND Feat.TASK_UID = NetworkElement.TASK_UID AND NetworkElement.AttrName ='NetElem'LEFT JOIN CPR_ContainerAttr Priority WITH (nolock) ON Feat.PROJ_ID =Priority.PROJ_IDAND Feat.TASK_UID = Priority.TASK_UID AND Priority.AttrName = 'FPA'LEFT JOIN ( CPR_PATH FeattoDoc WITH (nolock)INNER JOIN CPR_ENUM_AllDocs Doc WITH (nolock)ON FeattoDoc.SUCC_PROJ_ID = Doc.PROJ_ID AND FeattoDoc.SUCC_TASK_UID =Doc.TASK_UID AND FeattoDoc.EDGE_ID = 1AND Doc.ID_Code NOT LIKE '[<]%' AND Doc.Entity in('FDD','SRD','SRAD','FFRD','VRAD')LEFT JOIN CPR_DOCSORTNUM Sortnum WITH (nolock) ON Doc.Entity =Sortnum.DocEntINNER JOIN MSP_TASKS DocName WITH (nolock) ON Doc.PROJ_ID = DocName.PROJ_IDAND Doc.TASK_UID = DocName.TASK_UIDINNER JOIN CPR_ENUM_Task_RTF_Notes DocNotes WITH (nolock) ON Doc.PROJ_ID =DocNotes.PROJ_IDAND Doc.TASK_UID = DocNotes.TASK_UIDLEFT JOIN ( CPR_PATH DoctoDet WITH (nolock)INNER JOIN CPR_ENUM_TASK_Task_Type DocDetails WITH (nolock) ON(DoctoDet.SUCC_PROJ_ID = DocDetails.PROJ_IDAND DoctoDet.SUCC_TASK_UID = DocDetails.TASK_UID AND DocDetails.Task_TypeIn ('WriteRev', 'RwkRFA', 'PubLive', 'Waived','Review','RFA', 'CustRev'))LEFT JOIN MSP_TASKS DocDetDates WITH (nolock) ON(DocDetails.PROJ_ID = DocDetDates.PROJ_ID AND DocDetails.TASK_UID =DocDetDates.TASK_UID)LEFT JOIN CPR_ENUM_TASK_Durations DocDetDur WITH (nolock)ON ( DocDetails.PROJ_ID = DocDetDur.PROJ_ID AND DocDetails.TASK_UID =DocDetDur.TASK_UID))ON ( DocName.PROJ_ID = DoctoDet.PRED_PROJ_ID AND DocName.TASK_UID =DoctoDet.PRED_TASK_UID AND DoctoDet.EDGE_ID = 1))ON (Feat.PROJ_ID=FeattoDoc.PRED_PROJ_ID ANDFeat.TASK_UID=FeattoDoc.PRED_TASK_UID AND FeattoDoc.EDGE_ID = 1)WHERE Feat.FeatureID NOT LIKE '[<]%'ORDER BY PRName, FIDSort, FeatureID, DocSortNum, DocProjID,DocTaskUID,DateProjID, DateTaskUID

View Replies !

Performance Issue
Hello,On the database we have stored procedure that loads couple thousandsrows with information, it has to be done sequentially because duringthis load various variables and values has to be changed and modified.We have about 10000 rows to process every day - usually it takes about 1hour.Some time ago I have noticed a big decrease in performance of thisprocess (it takes about 10 to 20 more time to process 10K), profilershowed me that duration of each read from some of the tables is huge andthis is the reason why this process slows down. I have also amaintenance plan that updates statistics. When I run this job duringprocessing usually everything goes back to normal and reads are veryfast, performance is ok.Could someone explain why this is happening or give me a hint where Icould find the explanation/answer.Greetings Cray............:::::::: [ @ ] ::::::::...........Cray at o2.pl

View Replies !

Performance Issue
One of our client is having nearly l Million rows in a table. when search is happening on that table, it is taking hell lot of time for a simple query

I have to Search on one column(varchar) only, based on results I have bring some more fields info from another 3 tables

Non clustered Index also specified on that search column

I tried with all options

using Like operator
In operator
if two values given then using union
using fulltext indexing --- Contains

but in performance there is no much difference

even I used "top" 1000 rec to fetch

for 1000 records it is taking 20-30 sec

please help me out in this issue with any other methodology

View Replies !

SQL Performance Issue
My company has just started using SQL Server 2000 with our in house customized program.

We have users in two diffrent domains using it at this time for testing. The server located on the local domain seems to be faster for our users. However the other domain located a few hundred miles away seems to be very slow for our users.

We have a full T1 line that is not even 50% utilized. Users sometimes report that connections are sometimes dropped from SQL on this domain. I'm new with SQL Server. I was wondering if the problem can be helped on my end as far as administrating the SQL Server. I was thinking that it could be a programming issue. if you can give me some suggestions I would appreciate it.

Would setting up another server on the problem domain for replication with the server here increase performance enough to justify the cost?

View Replies !

Performance Issue
Can any one help me for this??

I've my application on Access 97 and database on SQL Server 7.0...
The problem is that the same Application when run on one database, its giving good response time but when run on a copy of the database with some 1000 record more then the Original Database gives very poor response time...

Thanks,
Neetu

View Replies !

Performance Issue
I created one stored procedure . If I run the stored procedure in SQL server Query Analyzer, It takes only 5 minutes.If I run the same stored procedure
using job(T-SQL),It takes 8 hours.Can you help me for this problem.Any help
appreciated

Thanks
Siva

View Replies !

Performance Issue
Is it better to create a new database for some additional processing on the same server or new objects (tables and SP's should be created in the existing database which is doing all kinds of processing). The current database is involved in Replication also.

View Replies !

Performance Issue
hi..

I have a table that has queries more than 250.000 for now..

when I perform a search in database it is deathly.

my structure like below

date|ip
-------
9/5/2005|67.66.44.22
9/5/2005|267.66.44.22
9/5/2005|76.66.44.22
9/5/2005|122.66.44.22
9/5/2005|87.66.44.22
.......
5/30/2005|.........
............

select * from tbl where ip='blabla' and date=getdate()

it takes too times..

although I need only this day records for ip blabla, my sql statement searches old days..

what is your suggestion for this ?

I don't want to create another database for old day and another database for today.
another way ?

View Replies !

SQL Performance Issue
Hi all,

This is VB code but my actual problem is with SQL query that am using in it. please have a look.

Set RecordsSet1 = oBP.GetSuppliersByNameRange (i_sSupplierNameFrom, i_sSupplierNameTo)

' GetSuppliersByNameRange - this method has a query. We can just use this method and can not modify it.

'And the customer’s database returns almost ten million records when this above line is executed.

String1 = getStringListFromRs (RecordSet1, "BISSUPPLIERCODE")

' getStringListFromRs – this method will return a string
' (which has nearly ten million values)

sSQL = “SELECT BISSUPPLIERCODE” & _

“FROM SETTINGSBYSUPPLIER” & _

“WHERE SETTINGSBYSUPPLIER.SELFBILLING = “& lSelfBillingValue & _

“AND SETTINGSBYSUPPLIER.PAYAFTERRECEIPTS = “& lPayAfterReceipts & _

“AND SETTINGSBYSUPPLIER.BISSUPPLIERCODE IN “& String1

'This IN operator has to search for a value in ten million records
'and hence it’s getting timeout.

Have to replace this IN operator with something else.

I tried using filters but it doesn’t help as there also I had to put it in either in while or in for loop.

thanks in advance for your help.

View Replies !

Serious Performance Issue
We are currently in the process of migrating our DTS packages to SSIS. The current package I am working on imports data from an Access database into two SQL Server 2005 tables. The number of rows being moved is roughly 3 million. The old DTS package ran in about 40 min. The new SSIS package takes roughly 2 hours to run. I have tried several different things to tune performance with no luck. Can anyone provide some insight on this.

View Replies !

LEN Performance Issue
I am trying to tune a sql statement that is using

len(prd.AssignmentAB) > 0

and this filter cost is too large. is their a better way to do this check, the field is a varchar.

Please help.

Emad

View Replies !

RDA Performance Issue
I'm using RDA to pull 20 tables to my Sql Server CE 3.0. When it has pulled all the tables the size of the database is about 500-800 kb. Not very much. I use GPRS and it takes about 4-5 minutes to pull 20 tables. What I've noticed when I step through the code in the emulator is that it's not the amount of data in each table that takes time, it seems sort of a connection issue?

I do not use Windows integrated security on the database server, I have a sql server user, is that a problem?

/Magnus

View Replies !

Performance Issue
Guys,

I am running the same query in the same copies of databases on 2 different instances it gives me different time periods
10 secs for on instance A and 90 secs for on instance B. The database on instance B is exact copy/restore of database on instance A.

The execution plans for the query is also same from Instance A and Instance B.

How do I start troubleshooting the performance on the Instance B?

Any suggestions and inputs would help.

Thanks

View Replies !

Performance Issue
Hi

I have a SP which is running slow, when traced in SQL Profiler, SP:Completed shows 6.3 secs and SQL:StmtCompleted shows 30 secs. I used Print statements (to print start and end time) in the SP in the very beginning and end and when executed shows time time diff of only 6.3 secs but overall execution takes 30 secs. Why is this happening?
Any suggestions?

-- Amol

View Replies !

Single Field Duplication Problem
I have a table with many fields but there is a single field that I do not want duplicates. If I index this specific field preventing duplicates, the entire record does not append. (The field in question is not keyed).

Thanks

Bill Howard

View Replies !

Performance Issue In SQL Server
Hi experts,
I am facing one performance issue.This is the scenario.
For eg., EmpId,EmpName,DeptId,DeptName,Sal are there.Except sal all the remaining columns values are same(I mean having same data).
Assume,here 4 rows are there in that table.
so I wrote a select query to get the data like 1,xx,1,yy,50,500,5000,1000
here 50,500,5000,1000... are the sal column values.
I wrote a function to get the sal values as comma separated ones if the remaining column values are same.But for Huge no' of records (assume for 25000 records), the performance is very bad.
So need a inbuilt query / any other solution for this problem.

View Replies !

Tempdb Performance Issue
My Sql 2005 SP1 has performance trouble this afternoon. I
noticed a lot PageLatch_up and PageLatch_sh waiting with 2:1:1. I think the
problem may be caused by the tempdb. There are more than 1000 temp tables. The number
of user connections is abnormally high. â€œSELECT *
FROM tempdb.dbo.sysobjectsâ€? cannot be finished due to blocking.

I restarted the server to fix
the problem temporaryly.

Any clue to fix the problem? Does it help to replace temp
table with table variable in stored procedures? â€œDrop temp tableâ€? are called in
procedures, why the temp tables are still in the tempdb?

Thanks for your help.

View Replies !

Strange Performance Issue
Hi,
I have a strange performance issue. I have the following query which takes 40+ seconds to run. SELECT Count(x)
FROM view WHERE x = 347
AND y = 10056
AND z = 2
AND w = '01'
But if i switch it to below, the query returns the one result quickly (1 second).SELECT x
FROM view WHERE x = 347
AND y = 10056
AND z = 2
AND w = '01'
If the view is returning results quickly, why is it so much trouble for SQL to run the aggregate function on the results?Any help is appreciated. -Brian

View Replies !

Please Help Sql2005 Performance Issue
I moved database from ms sql2000 to 2005 several days ago. I backed up database in 2000 and restore it in 2005. Everything is OK except the performance. Rebuilding statistics has no big help. I noticed that there were some pagelatch_up and pagelatch_sh waits. Ave latch wait time is about 3.3ms. Latch Waits/sec is about 8900. Total Latch wait time is about 30000ms. And the sql user connections in 2005 are much higher than in 2000.

Any suggestion about this issue?

View Replies !

Query Performance Issue
Hello,
Here I have a small doubt about validating UserName and Password.I validate username and password with following quey (forget about case-sensitiveness of password) :select password from table where username='Uname' and password='pwd';Now in second scenario, I use following :select password from table where username='Uname'and validate password in .NET code.1) If user having 'Uname' does not exists in database then which query is faster (first or second)?2.1) If user exists and password is not matching then which is faster?2.2) 2.1 + If there is clustered index on username column, is first query optimized?
Thanks

View Replies !

Performance Issue, Is It Indexing?
Hi,

In SQL Server 2000, I have a table called SupplierData that has about 100000 records, and its structure looks like this:

CREATE TABLE [dbo].[SupplierData] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Supplier] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [nvarchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Category] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ItemID] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPC] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mftr] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Genre] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Retail] [smallmoney] NULL ,
[Cost] [smallmoney] NULL ,
[PreOrder] [smalldatetime] NULL ,
[Release] [smalldatetime] NULL ,
[Disc_FinalReturn] [smalldatetime] NULL ,
[GIS] [bit] NULL
) ON [PRIMARY]

Anyways, I recently imported some records (4000 or so) into it which didn't have a UPC Code (NULL) , Since then, all the Stored procedures in my database that use this table have been incredibly slow. I'm talking like things that used to take 2 minutes to run now take an hour.
I'm not really sure what is causing this, but since it work fine before I imported the records I have an idea that it has something to do with that.
I'm wondering if maybe since there was a index on UPC code, maybe it is choking on all the Nulls? I'm still just learning about the wonderful world of indexes.

The types of queries I'm running on this table are not that involved, mainly stuff like:

SELECT Cost, ItemID FROM SupplierData WHERE UPC = @UPC and Supplier = @SupplierName ORDER BY Cost
SELECT Top 1 Cost FROM SupplierData WHERE UPC = @UPC ORDER BY Cost

Any thoughts?

Thanks

View Replies !

SQL Server Performance Issue
I'm having a performance Issue with my Production SQL Server (2 x XeonDual-Core Woodcrest 1.6Ghz, 2GB RAM, IIS, SQL Server). In general thequerys take much longer than the querys in my development server . Forexample a recursive UDF takes 20s in my development server and 2m inmy production server (both with same users load) but my productionserver it's much hardware powerfull than the other server.I start monitoring an realized that the Prod. Server consumes a lot ofPhysical Disk Reads and Writes when i execute this example UDF query,then I realized that the Prod. Server has a SATA RAID1 Disk Mirroringand my Dev. Server do not has mirroring.It seems that the RAID1 disk performance seems to be very importantwhen I execute this query, and my question is WHY??If the query only reads one Table 'CentroCostos' witch has 1255records, why DISK performance is so important? It should work withthis info on memory and not have to use so much disk i/o.Please help me understand this to solve this problem.Thanks, ARSET QUOTED_IDENTIFIER OFFgoSET ANSI_NULLS OFFgoCREATE FUNCTION dbo.fn_CentroCosto (@ccs_ids VARCHAR(4000))RETURNS @Ret TABLE (ccs_id INT)ASBEGINDECLARE @ccs_ccs_id INT, @cantidad INT, @ccs_id INTIF @ccs_ids = 'null'RETURNSELECT @cantidad = COUNT(*) FROM dbo.fn_split(@ccs_ids,',')IF @cantidad = 1BEGINSELECT @cantidad = COUNT(*) FROM CentroCosto WHERE ccs_ccs_id= @ccs_idsIF @cantidad = 0BEGININSERT INTO @Ret SELECT @ccs_idsRETURNENDELSEBEGININSERT INTO @Ret SELECT @ccs_idsDECLARE ListadoCcs CURSOR FOR ( SELECT ccs_id FROMCentroCosto WHERE ccs_ccs_id = @ccs_ids )OPEN ListadoCcsFETCH NEXT FROM ListadoCcs INTO @ccs_idWHILE @@FETCH_STATUS = 0BEGININSERT INTO@RetSELECTa.ccs_idFROMdbo.fn_CentroCosto(@ccs_id) As aFETCH NEXT FROM ListadoCcs INTO @ccs_idENDCLOSE ListadoCcsDEALLOCATE ListadoCcsENDENDELSEBEGINDECLARE ListadoCcs CURSOR FOR ( SELECT a.Value FROMdbo.fn_split(@ccs_ids,',') AS a )OPEN ListadoCcsFETCH NEXT FROM ListadoCcs INTO @ccs_idWHILE @@FETCH_STATUS = 0BEGININSERT INTO@RetSELECTccs_idFROMdbo.fn_CentroCosto(@ccs_id)WHEREccs_id not in (select ccs_id from@Ret)FETCH NEXT FROM ListadoCcs INTO @ccs_idENDCLOSE ListadoCcsDEALLOCATE ListadoCcsENDRETURNENDgoSET ANSI_NULLS OFFgoSET QUOTED_IDENTIFIER OFFgoIF OBJECT_ID('dbo.fn_CentroCosto') IS NOT NULLPRINT '<<< CREATED FUNCTION dbo.fn_CentroCosto >>>'ELSEPRINT '<<< FAILED CREATING FUNCTION dbo.fn_CentroCosto >>>'go

View Replies !

Performance Complex SQL Issue
Hi,I have a table ORDER_DETAIL with 22 million rows which has an indexof(person_id, code_id, created_dtt)I have another ORDER table with 5 million rows which has an indexof(order_dtt, person_id)I have a small CODES table with 1000 rows which allows me to getthe 50 or so codes I need. My query needs to be something like this:select od.person_id, od.code_idfrom order_detail od, order o, codes cwhere o.order_dtt between sysdate-365 and sysdateand o.person_id = od.person_idand od.code_id in (select code_id from codes where code_type ='MYCODE')and od.create_dtt between sysdate-365 and sysdateBut is this using the full index on the ORDER_DETAIL table? Should Ibe using EXISTS in some fashion instead?Accessing the ORDER_DETAIL table is a pain because it is so large, thecode_id's I need are a relatively small number but the date range isabout 25% of the table - same with the ORDER table. However it is theonly way I can get to filtering down to the code_id.This is a simplification of the problem - but accurate - addingadditional indexes is not an option.thanks!Tim

View Replies !

EMC Clarion Performance Issue
I am in the process of testing an EMC Clarion install with our DataWarehouse. Performance is fast and consistent for queries using aclustered index but very poor for queries using non-clustered indexes.Performance on non-clustered indexes is very slow and inconsistentcompared to our current production environment using EMC Symmetrix.I eliminated the server and SQL install as the issue by testing thesame queries on local disk on the server with good results. I amrunning SQL2000 SP3. EMC has not helped yet. Any ideas?Thanks

View Replies !

Performance Issue (repost)
Hi,A table which I schedule DB maintenance to REORGANIZE DATA AND INDEXPAGES with THE ORIGINAL OF FREE SPACE every night, but during the day, I check with SHOWCONTIG the result is as following:DBCC SHOWCONTIG scanning 'INV_HEAD_TRANSACTION' table...Table: 'INV_HEAD_TRANSACTION' (1827537594); index ID: 1, database ID:10TABLE level scan performed.- Pages Scanned................................: 259- Extents Scanned..............................: 40- Extent Switches..............................: 98- Avg. Pages per Extent........................: 6.5- Scan Density [Best Count:Actual Count].......: 33.33% [33:99]- Logical Scan Fragmentation ..................: 31.27%- Extent Scan Fragmentation ...................: 32.50%- Avg. Bytes Free per Page.....................: 1177.9- Avg. Page Density (full).....................: 85.45%DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator.As you can see the Scan Density is lower from about 80% up (resultfrom scheduled DB maintenance) to 33.33% in about 3 hours (8.00 -10.00).Is this an usual result (the process of this table is mostlyINSERT/UPDATE) because users complain about slow performance?Can I do anything else to the DATABASE during the working day?Any suggestion or advise would be very appreicated.Thank you in advanceNipon Wongtrakul

View Replies !

Performance Issue In My MDX Query
Hello,
I'm new to OLAP systems and MDX, and am doing some testing on Microsoft Analysis Service 2000 SP3, the database is Microsoft SQL Server 2000 SP3. In the cube I designed, the fact table contains purcahse information including the cost and quantity of the parts and the suppliers of the parts. There are 2 measures, qtyAvailable and cost. Two dimensions are involved, which are part and supplier. Here is what I 'm going to do:
1. calculate sum(qtyAvailable * cost * 0.0001) for all the items in the fact table, let us call this value sum1
2. find out in the fact table all of those parts with their sum( qtyAvailable * cost ) greater than sum1

Here is the MDX to do the 2 things above:
with member [Measures].[prod1] as '[Measures].[qtyAvailable] * [Measures].[cost]'
with member [Measures].[prod2] as '[Measures].[prod1] * 0.0001'
with member [Measures].[sum1] as 'sum(crossjoin([part].members, [supplier].members), [measures].[prod2])'
with member [Measures].[sum2] as 'sum(crossjoin([part].currentmember, [supplier].members), [measures].[prod1])'
select {[Measures].[sum2]} on columns,
Filter({[part].members}, ([measures].[sum2]>[Measures].[sum1]) ) on rows
from cube1

It takes 9 seconds to calculate only sum1 by using another MDX. The value of sum1 is 8256865.23.
If I replace sum1 in the MDX provided above with 8256865.23, it takes several minutes to finish.
But it keeps running for hours if I run the MDX query above with [sum1] instead of 8256865.23. So the calculation of sum1 seems to be the bottle neck. In my MDXquery, it iterates thru the members of the dimension [part]. I don't know whether [sum1] is calculated repeatedly for each iteration or not. However, Sum1 will be constant during the running of the whole MDX query. So sum1 only needs to be calculated once. I tried to use cache to improve the performance but it didn't work.
Can anyone help to tell whether there is anyway to optimize this query?
Thanks so much
Roy

View Replies !

SQL Server Performance Issue
We've got a clustered environment in which we run several SQL servers. However, one of the platforms, Greats Plains hangs for no reason. Is there a tool out there that we can use to determine what is causing the load ?

Thanks

View Replies !

Performance Issue With Unions In Sp's?
I was having a chat with a chap over lunch today and he asked if I knew of any performance issues when doing unions in stored procedures. I couldn't think of anything but he seemed sure there was.

Is there such an issue I've missed?

Mike

View Replies !

SQL Server - Performance Issue
Hi,

We have got a Server with couple 731MHz Pentium III + 1GB Memory and about 25-30 users connecting to it. The Processor Queue Length has always been more than 70%-99%. Is there any fix for this problem, like increasing any buffer sizes and so forth or should I add more processor power to that server?

Thank you in advance,

Raman.

View Replies !

Performance Issue Sybase 11.5 Vs MS SQL 7.0
We are in a beta tranformation from Sybase to MS SQL. An application that imports
data in the Sybase database tables takes 5 min to import 30,000 rows. However
when we are using MS SQL 7.0 for this same application, it is taking an hour to
complete it. Any ideas, anyone ?

Will appreciate
Thanks
Gohar
gayub@merrin.com

View Replies !

Performance Issue With Qty Counts By Day
I have an issue with the performance of the below script. I was under the impression that 1 select query would be fast than a while loop, but right now, that isn't the case... I'm on sql 2005 sp2, but the query plan is mostly the same on sql 2000 sp4 as well.

I've tried as best as possible to reproduce my situation with the below script. The data is limited to 2000 rows in CustomerDates, but in our environment, there are over 2million rows of customer activity. I want to summarize the Quantity owned per day, even if there was no activity.

Even though the below script is a small subset of data, the query plan given for it is the same as when there is a full set of data. The biggest hog of the execution is the Hash Match (Aggregate) section, being somewhere around 2-6 cost for this small set of data. That number times 1000 makes for a pretty massive query cost.

Does anyone have some ideas of what could be done to speed up the last query (there are 2 copies of it - each one using a different index).

SQL Code:

Original
- SQL Code

CREATE TABLE Integers
(
i int PRIMARY KEY
)

insert into Integers
values (0)
insert into Integers
values (1)
insert into Integers
values (2)
insert into Integers
values (3)
insert into Integers
values (4)
insert into Integers
values (5)
insert into Integers
values (6)
insert into Integers
values (7)
insert into Integers
values (8)
insert into Integers
values (9)

SELECT DATEADD(DAY, dt.i, '12-1-2006') as dtDate
INTO Dates
FROM
(
SELECT ones.i + tens.i * 10 + hundreds.i * 100 as i
FROM integers ones
CROSS JOIN integers tens
CROSS JOIN integers hundreds
) dt
WHERE dt.i < 150--limit the data to extend only into April 2007

CREATE UNIQUE CLUSTERED INDEX cnxDates ON Dates(dtDate)

SELECT CustomerID,
CASE WHEN CustomerID > 100 THEN ProductID ELSE 1 END as ProductID, --override productId of 1 to be 2 for some customers
CASE WHEN CustomerID > 100 OR ProductID = 1 THEN 10 ELSE -10 END as Qty,
CAST(CASE WHEN CustomerID > 100 OR ProductID = 1 THEN '1-1-2007' ELSE '3/1/2007' END AS datetime) as ActivityDate
into CustomerDates
from
(--make some customerIDs
select 1 + ones.i + tens.i * 10 + hundreds.i * 100 as CustomerID
from integers ones
cross join integers tens
cross join integers hundreds
) c
cross join
(
select i as ProductID
from integers dbl--double the records
where i IN (1, 2)
) p

CREATE UNIQUE CLUSTERED INDEX cnxUnique ON CustomerDates(CustomerID, ActivityDate, ProductID)

--make a copy of the table w/ a different style of index
SELECT * INTO alt_CustomerDates FROM CustomerDates
CREATE UNIQUE CLUSTERED INDEX cnxUnique ON alt_CustomerDates(ActivityDate, CustomerID, ProductID)

SELECT c.CustomerID, d.dtDate, SUM(c.Qty) AS quantity, c.ProductID
FROM Dates d
INNER JOIN CustomerDates c
ON c.ActivityDate <= d.dtDate
GROUP BY c.CustomerID, c.ProductID, d.dtDate
HAVING SUM(c.Qty) > 0

SELECT c.CustomerID, d.dtDate, SUM(c.Qty) AS quantity, c.ProductID
FROM Dates d
INNER JOIN alt_CustomerDates c
ON c.ActivityDate <= d.dtDate
GROUP BY c.CustomerID, c.ProductID, d.dtDate
HAVING SUM(c.Qty) > 0

CREATE TABLE Integers( i int PRIMARY KEY) INSERT INTO Integers VALUES (0) INSERT INTO Integers VALUES (1) INSERT INTO Integers VALUES (2) INSERT INTO Integers VALUES (3) INSERT INTO Integers VALUES (4) INSERT INTO Integers VALUES (5) INSERT INTO Integers VALUES (6) INSERT INTO Integers VALUES (7) INSERT INTO Integers VALUES (8) INSERT INTO Integers VALUES (9) SELECT DATEADD(DAY, dt.i, '12-1-2006') AS dtDateINTO DatesFROM ( SELECT ones.i + tens.i * 10 + hundreds.i * 100 AS i FROM integers ones CROSS JOIN integers tens CROSS JOIN integers hundreds ) dtWHERE dt.i < 150--limit the data to extend only into April 2007 CREATE UNIQUE CLUSTERED INDEX cnxDates ON Dates(dtDate) SELECT CustomerID, CASE WHEN CustomerID > 100 THEN ProductID ELSE 1 END AS ProductID, --override productId of 1 to be 2 for some customers CASE WHEN CustomerID > 100 OR ProductID = 1 THEN 10 ELSE -10 END AS Qty, CAST(CASE WHEN CustomerID > 100 OR ProductID = 1 THEN '1-1-2007' ELSE '3/1/2007' END AS datetime) AS ActivityDateINTO CustomerDatesFROM (--make some customerIDs SELECT 1 + ones.i + tens.i * 10 + hundreds.i * 100 AS CustomerID FROM integers ones CROSS JOIN integers tens CROSS JOIN integers hundreds ) c CROSS JOIN ( SELECT i AS ProductID FROM integers dbl--double the records WHERE i IN (1, 2) ) p CREATE UNIQUE CLUSTERED INDEX cnxUnique ON CustomerDates(CustomerID, ActivityDate, ProductID) --make a copy of the table w/ a different style of indexSELECT * INTO alt_CustomerDates FROM CustomerDatesCREATE UNIQUE CLUSTERED INDEX cnxUnique ON alt_CustomerDates(ActivityDate, CustomerID, ProductID) SELECT c.CustomerID, d.dtDate, SUM(c.Qty) AS quantity, c.ProductIDFROM Dates d INNER JOIN CustomerDates c ON c.ActivityDate <= d.dtDateGROUP BY c.CustomerID, c.ProductID, d.dtDateHAVING SUM(c.Qty) > 0 SELECT c.CustomerID, d.dtDate, SUM(c.Qty) AS quantity, c.ProductIDFROM Dates d INNER JOIN alt_CustomerDates c ON c.ActivityDate <= d.dtDateGROUP BY c.CustomerID, c.ProductID, d.dtDateHAVING SUM(c.Qty) > 0

View Replies !

Performance Issue When OLE DB Used Across The Network

I'm not sure if this is right forum for this question.

Bear with me for a second so I describe the condition of the issue.

I access SQL Server via OLE DB from C++. I have simple stored procedure that looks like:

Code Block
create procedure mysp(@param1 int, @param2 int, ..., @param8 int) as
begin
insert into mytable (field1,..., field8) values (@param1, ..., @param8);
end

Application uses ATL::CCommand to execute it in a loop using the following syntax:

"EXECUTE mysp value1, value2,... value8;"

If I execute this application and SQL server is located on the same computer where the application is running, speed is about 8000 inserts in a second.

If I execute this application and SQL server is located on another computer (in the local network), speed is about 300 inserts in a second.

And here are 4 facts that drive me crazy:

1) If application runs several threads with these loops, speed increases to about 900 inserts/second, and then doesn't grow no matter how many additional threads are added (yes, each thread has it's own CSession).

2) Network bandwidth is utilized for about 0.2%; CPU load is below 5% (on both client and server); memory load is about 10% (on both client and server).

3) (Next observation I found by accident). If I combine calls, i.e. I send in ONE call query that looks like:

"EXECUTE mysp value1a, value2a,... value8a;EXECUTE mysp value1b, value2b,... value8b;EXECUTE mysp value1c, value2c,... value8c;"

speed increases! If I run this query in about 10 threads and combine several "execute" into one call (I tried as many as 20 "execute " statements into one call), speed increases to almost 8000 inserts in a second, i.e. becomes almost just like for a local server.

4) The most funny - I have very similar results when backend is PostgreSQL (8.2.4) (i.e. 300 inserts/second from one thread, 900 inserts/second max from many (>10) threads, increase in speed when combine several statements into one call). One exception - with PostgreSQL I was unable to reach 8000 inserts in a second - it gets stalled at 7000 inserts in a second on the same hardware (but it doesn't relevant to my issue).

In my understanding, having MS SQL Server and PostgreSQL to show same issues, I think problem is somewhere in the OLE DB provider, or may be ATL implementation of connectivity, or somewhere else on the client. Again, hardware was not a problem anywhere - limits were very far from being pushed. Threads are completely independent, no locking whatsoever (in my code). May be ATL uses some sync. locking, but I'm not aware of it. And again, it doens't explain difference between performance for the local and networked clients.

Anyone has any ideas what the problem can be?

Thanks!

View Replies !

SSIS Performance Issue
Hello,

I have been running massive ssis packages and testing the performance.

This is my execution design:

I have a main package that gets a list of packages to execute from a table.

Then using a foreach loop in send the package to execute ( somewhere in the middle i delete the corresponding old log file for that package ), each of the packages configures themselves from the parent package variables.

What i have been analysing tells me that for example a package runs in 2 minutes and then the time wasted from the end of the package to the start of the other task is in average 3 to 6 minutes... thats alot... since i run about 20x12 packages witch gives me of wasted time about 20 hours.

My question is... what can be causing the delay between the end of package and the start of the other one...

The tasks types i am using in the execution controller package are:

Foreach loops, For Loop, File System task, Execute Package Task and some Execute SQL Tasks

Best Regards,

Luis SimÃµes

View Replies !

Insert Into : Performance Issue
Hi,

I have a insert into statement in sp1 which executes sp2.sp2 retuns around 2000 rows after lot of processing.When I execute sp2 directly from query analyser it takes about 2-3 mins but if I do the
insert into tab1
exec sp2

It takes 20 mins.

what are my other options, how can i improve the performance?

Thanks,
ssm

View Replies !

Peculiar Performance Issue.
Hi all,

We have a issue with the performance in SQL server database.

Scenario & Issue:
We have delivered a .net application to our client. This application is installed in newly built windows 2003 server.

The client is facing performance issues with the application. When compared with the performance in the development server , the performance of the production server is very poor.

Even when we execute the stored procedures in the backend, the performance is poor in the production server.

Example: A stored procedure that takes 16 seconds in the development server takes 17 minutes for the same parameters. The time remains the same even for HOT execution.

System Info:

Database Version - SQL Server 2005
Database Size - 120 plus GB
OS Platform - Windows 2003
Database Load - 50 users
CPUs - 4
RAM - 8 GB

The OS is Clustered ( failover clustering ).

Points to Note:

1.There is a huge table with 250 million rows ( this table itself takes upto 60 GB )

2.The huge table is partitioned ( SQL server 2005 table partitioning ) and placed in 20 different filegroups (.mdfs).

3.The .mdf's are placed in a SAN and .ldf is in local HD

4.Dynamic queries are used at few instances for performance benefits.

Questions:

1. Any thoughts on why this kind of performance issue arises ?

2. The client DBA wants us to clear the data and stored procedure cache before executing the stored procedure and test the performance.

Will this be would be the case in production scenario ?

3. Will the performance change based on the input parameters ?

4. The client DBA also have stated that a report server that pings the production database server is the cause for frequent clearing of the SQL Server cache.

When does the SQL Server database actually clears the cache memory? Is there any way to control it?

Any help would be highly appreciated.

Please let me know if you have any questions.

Thanks & Regards,

DBLearner

View Replies !

Database Performance Issue
Hi everybody,
We are having a application performance issue, where the user's are complaining that the application is really slow. They access the application thru citrix. I ran sp_who on the database side to see if there are any blocks, but the block column does'nt have anything greater than o. So trying to see how I can track where the problem is. Please help. Thanks.

View Replies !

Performance Issue With 2 Datasets
I have a report with 2 datasets and am using a multi-valued parameter, referenced in both datasets. When I run each query in Management Studio, they run in approx 2-3 seconds. With the first dataset in Visual Studio and a layout of the data returned by the first dataset, my response time is approx 5 seconds. After adding the second dataset to the report and making the same selections, my response time increases to 2 minutes.

Any idea what could be causing this? The field names are different for each query and each query only returns one row of data.

View Replies !

Performance Issue On BLOB

Hi there,

I have a table with 3 fields:

CREATE TABLE images
(id INT PRIMARY KEY,
blobSize INT NOT NULL,
image MEDIUMBLOB);

which is filled with ~4000 rows with images about 70-80kB in size.

What I can't understand is why it should take so much time to get a single image from the database. It actually takes about the same amount of time to get one image as it does to get them all. I'm using the following statements:

SELECT * FROM images;
4071 rows in set (11.52 sec)

SELECT * FROM images WHERE id=10;
1 row in set (4.63 sec)

SELECT blobSize FROM images;
4071 rows in set (0.02 sec)

Is there something to do about this seemingly bad performance? I understand I could store the path to the image file on the hard drive, but I thought it'd be nice to only have one file to make a backup of instead of thousands. This issue might force me to reconsider if nothing can be done to speed things up.

I'd be grateful if anyone could help me out.

Best Regards,
Kristian Tarning

View Replies !

Strange Performance Issue
Hi,

I've got a strange performance issue:

I'm using a SQL statement with a CTE to recursively get all node-ids from a tree beginning with a root node. In a select statement I'm using this CTE to get in a SELECT ... WHERE nodeID IN (SELECT ID FROM CTE_Nodes) statement data that is according to the nodes related to the root-node.

In our ASP.NET 2.0 application these statements are very slow or time out with an Exception. When I'm executing the same statement in a Management Studio the statement executes in less than one second.

BTW, we're using SQL Server 2005 Standard Ed. (9.0.3050 + 9.0.3054) in SQL Server 2000 compatibility mode with SQL Authentication.

I'm a bit frustrated, because the statements are the same.

Thanks in advance,

Klaus

Update:

BTW, the SQL Server is on a server machine and the Management Studio and the ASP.NET application on my developer machine. For data access we're using Enterprise Library 2.0 with System.Data.SqlClient.

Which possibilities do we have to trace this issue? Please help.

View Replies !

Performance/Optimization Issue
I am able to get this to work by using nested loops but they are very inefficient and with the size of my tables I cannot afford to use them. There must be a more efficient solution?

I have two tables...

   Initial_Procedure
   ID
   Person_ID
   Completed_DTTM

   Procedure_2
   ID
   Person_ID
   Completed_DTTM

I need the result to be...

   Tbl_Final_Result
   Initial_Procedure.ID
   Initial_Procedure. Person_ID
   Initial_Procedure. Completed_DTTM
   Procedure_2.ID
   Procedure_2.Person_ID
   Procedure_2.Completed_DTTM

Some general rules...
(Hope these are clear enough)

   - Each person has at least one Initial_Procedure.

   - There may be zero, one, or more Procedure_2 for each Initial_Procedure.
      - If there is more than one Procedure_2 for an Initial_Procedure get the most recent.

   - To link Procedure_2 to Initial_Procedure the Initial_Procedure.Completed_DTTM < Procedure_2.Completed_DTTM and Initial_Procedure.Person_ID = Procedure_2.Person_ID

   - If there is more than one Initial_Procedure where Initial_Procedure.Completed_DTTM < Procedure_2.Completed_DTTM:
        Procedure_2.Completed_DTTM Between row 1: Initial_Procedure.Completed_DTTM and
          row 2: Initial_Procedure.Completed_DTTM --(assuming Initial_Procedure is in order)
          AND Initial_Procedure.Person_ID = Procedure_2.Person_ID

Some example data.....

   Declare @Initial_Procedure table (ID int, Person_ID int, Completed_DTTM datetime)
   Insert into @Initial_Procedure
   Select 1, 1, '01/10/2007' union all
   Select 1, 1, '02/15/2007' union all
   Select 1, 1, '02/20/2007' union all
   Select 1, 2, '01/02/2007' union all
   Select 1, 3, '06/26/2007' union all
   Select 1, 4, '03/14/2006' union all
   Select 1, 4, '10/10/2006' union all
   Select 1, 4, '08/27/2007'

   Declare @Procedure_2 table( ID int, Person_ID int, Completed_DTTM datetime)
   Insert into @Procedure_2
   Select 2, 1, '01/09/2007' union all
   Select 2, 1, '01/15/2007' union all
   Select 2, 1, '01/16/2007' union all
   Select 2, 1, '01/17/2007' union all
   Select 2, 1, '02/19/2007' union all
   Select 2, 1, '07/25/2007' union all
   Select 2, 1, '09/02/2007' union all
   Select 2, 2, '01/01/2007' union all
   Select 2, 2, '01/14/2007' union all
   Select 2, 2, '01/20/2007' union all
   Select 2, 3, '05/04/2007' union all
   Select 2, 3, '06/27/2007' union all
   Select 2, 4, '11/06/2006'

The final result should be...

   Tbl_Final_Result
   IP.ID IP.Person_ID IP.Completed_DTTM P2.ID     P2.Person_ID P2.Completed_DTTM
   1       1                   01/10/2007                 2          1                     01/17/2007
   1       1                   02/15/2007                 2          1                     02/19/2007
   1       1                   02/20/2007                 2          1                     09/02/2007
   1       2                   01/02/2007                 2          2                     01/20/2007
   1       3                   06/26/2007                 2          3                     06/27/2007
   1       4                   03/14/2006                 NULL    NULL               NULL
   1       4                   10/10/2006                 2          4                     11/06/2006
   1       4                   08/27/2007                 NULL    NULL              NULL

View Replies !

Huge Performance Issue
I have a medical DB with the loads 150,000 transactions per month. Each month, I load the tranactions into a table for the current year. I also have to update records for prior months based on current month information.

For example, out of 186,000 dump records...150,000 will be loaded into the main table and 36,000 will be used to update records already loaded into the main table.

The tables have 90 columns, I have a clustered PK using [Soc_Sec_Number] & [Month] & [Row Index]. I need the row index counter (like auto number in MS Access) because I can have multiple transactions per month for the same Soc Sec Number.

===========================================================

My steps are

1) Load 150,000 records into main table (For december, this makes the table have 1,800,000 rows
2) Run queries for the remaining 36,000 rows to update records already loaded into the table containing the 1,800,000 rows.
3) The 36,000 queries have to be splits depending upon the update type code, So I am actually running 6 queries using 6,000 rows each against the 1,800,000 records.

The update queries are using inner joins with [Soc Sec #] and [Date], part of my composite Primary Key on both tables.

=============================================================
Problem

This process takes forever, about 4 hours per monthly update. As the months go out, the main table gets larger and the time increases. It took almost 24 hrs to get from January 2004 to June 2004.

I am running Sql Server on my PC, no seperate workstation. My PC has 2.8 GHZ with about 1 Gig in RAM. Could my PC specs be too low. I noticed that the task mamager shows sqlservr.exe using over 657,000 mb of RAM when running.

I also ran a simple Select MAX(Soc_Sec_Number) query that took over 5 minutes. This is way too long especially since Soc_sec_Number is part of the composite PK.

Could my queries actually take that long or are my pC specs too low. MY PC seemed to freeze after the JUNE update? Any help appreciated.

View Replies !