Tuning (Urgent)
Jan 15, 2002
I need to find a way to make this query run faster. Please let me know if you can help. Here are some of the issues.
1) I have a database with one table, and No indexes can be put in place.
2) This is a very large database. (4,000,000) records, and growing daily.
3) the following query returns 720,000 records.
4) The query takes about 18 minutes to complete.
I understand that by doing a table scan, the way this has to run is difficult, but is there anything I can do?
The Problem: When I run this query, I also run performance monitor, I have found that my page file is at 100%. I have 1 Gig of Ram on the server with 2 P III Xeion Processors. I don't know what other information I can give, but please let me know.
Thanks for you assistance
Troy
View 2 Replies
ADVERTISEMENT
Jan 18, 2002
Tuning Question for Gurus!
We have a stored procedure which has the following "where" condition a,b and c are same table joined to itself.
This was taking 1 hour to run previously(which caused severe blocking on the server).Then, I modified the "where" condition as follows to improve the performance:
OLD CODE(where condition):
---------------------------
Where getdate() >= a.Start_DT
AND getdate() >= b.Start_DT
AND getdate() >= c.Start_DT
AND(getdate() <= a.end_DT OR a.end_DT IS NULL)
AND(getdate() <= b.end_DT OR b.end_DT IS NULL)
AND(getdate() <= c.end_DT OR c.end_DT IS NULL)
MODIFIED CODE(where condition):
------------------------------
Where getdate() >= a.Start_DT
AND(getdate() <= a.end_DT OR a.end_DT IS NULL)
AND getdate() >= b.Start_DT
AND(getdate() <= b.end_DT OR b.end_DT IS NULL)
AND getdate() >= c.Start_DT
AND(getdate() <= c.end_DT OR c.end_DT IS NULL)
This new code has improved the performance,and now the query is running in 1 sec!
BUT now the big question is:
------------------------
All of a sudden the old stored procedure is also started running in 1 sec! Any clue on why it happened? Also,which code is superior, performance wise??
I need to explain the reason to my management,as this has been a major problem all these days.Also,it will help them to put the right code in production.
Thanks!!
sa.
View 2 Replies
View Related
Nov 22, 2000
Hi
Is there any good books for Query Tuning and Stored procedure Tuning
Thanks
View 1 Replies
View Related
Sep 27, 2000
This morning I can not connect to our SQL Server 7.0 whatever from client or server. The error message which I list below:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
A connection could not be estabished to server--Timeout expired
Please verfy SQL Server is running and check your SQL Server registration properties and try again.
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
We use windows NT authentication. We did not do any change on NT. The SQL Server daily schedule job usally stoped at 10:00AM, but today from the Window NT Task Manager, we can see that the SQL Server is still running untill now.
Please help!!!
View 3 Replies
View Related
Feb 21, 2005
Hey,
SQL Server 7.
I have a view which joins 3 tables. One has 15 million rows the next another 5 million and the third 500k.
When I join them the execution plan tells me that 15 million rows were retrieved from the first (taking about 5 mins) 1.5 million from the 2nd taking 3 mins and 4.5 million from the third taking almost no time.
The first two ause a clustered index, one being a seek the other a scan and the third a regular index seek. All followed by a hash match/inner join which takes 2 mins.
Any ideas on optimizing the SQL?
Here is the syntax:
SELECT b.packno, b.COMM_DATE, a.ben_grp_cr, a.ben_dsc_cr, c.gst_inc,
SUM(a.credit)
FROM TABC c INNER JOIN TABB B
ON b.PACKNO = c.PACKNO AND
b.COMM_DATE = BENDTL.COMM_DATE AND
b.BEN_NUM = c.BEN_NUM INNER JOIN
TABA a ON b.tran_id = a.tran_id
WHERE b.tran_date > '20040401' AND c.gst_inc = 0
GROUP BY b.packno, b.COMM_DATE, a.ben_grp_cr, a.ben_dsc_cr,
c.bendtl.gst_inc
View 6 Replies
View Related
Jun 27, 2006
I tried on finding out the problem in a slow running sp with profiler. I found that there are some waiting resource 'tracewrite' and 'async_network_io'. Any idea on it? Thanks in advance
View 4 Replies
View Related
Jun 7, 2001
I want to use the Index tuning wizard on some of my tables. Is it OK if I use when people are on the server or to do it during off-pick period. Thanks!!!
View 1 Replies
View Related
Jul 9, 2001
Hey all,
I am interested in finding out if anyone out there has experience with extremely high-performance SQL Server applications. The I/O needs of my database server are growing very quickly, and I am on the verge of launching a major upgrade project.
We have done all the standard tuning tasks: proper indexing, stored procedure tuning, etc... and are running on good small-server scale hardware ( dual PIII 700s, 1G RAM, but no RAID). The only path I can see to achieving higher performance are:
- lots of RAID, perhaps on a SAN.
- server upgrade, maybe 4 proc? I've been looking at RAIDZONEs and Netfinity's
- data partitioning ( I REALLY want to avoid this if I can! )
What do you do when you need Major Enterprise scale database performance from SQL Server? I've found lots of resources for Oracle and DB2, but I can't find many case studies for serious SQL Server installations.
Help!
-Dave
View 1 Replies
View Related
Jul 18, 2000
Hi,
does the upgradition SQL Server 6.5 to 7.0 will simply solve some problems which we are facing currently like ODBC errors Insert failed and update failed and also supporting more users ?
We have Access front end to SQL Server backend, so do we need to touch code in front end for optimizations ?
Can any pls guide me on this
Thanks
View 3 Replies
View Related
Jan 25, 2000
Does anyone know of any good reference material on how to tune SQL Server 7?
View 1 Replies
View Related
Nov 29, 1999
Other than the SQL Server 7.0 Index Tuner wizard (which isn't suggesting anything). Is there a 3rd party Index Tuner piece of software out there? Or is there something special that needs ot be done to get the SQL Index Tuner to work?
View 3 Replies
View Related
Sep 1, 2000
Good Morning Everybody,
I had a single table consists of one million records. To retrive data from that table it takes lot of time. how i can reduce execution time?
what is the procedures to tune the database. i implemented cluster index on primary key of that table. still i can't able to reduce execution time.
can anybody help me in this issue?
View 2 Replies
View Related
Nov 15, 1999
Hi, i am working on sql server 6.5 version.actually this is developed just one year back. but now the system is almost dead(low performance).i think the reasons r database design,networking,hardware etc.is it correct. and how to rectify these errors. i am suggesting that upgradation is the best option.so pl give the suggestions asap.
Thanx
Janreddy
View 1 Replies
View Related
Feb 27, 2001
Hi,
Anyone know any articles on Performance Tuning on the web? I'm trying to monitor one of my production boxes and don't know which counters to use.
Thanks!
Joe R.
View 4 Replies
View Related
Feb 13, 2007
Hi All,
What are some of the things i can do to improve query performance if the querey performance is realatively slow today compared to yesterday's performance:
Here are some of things i looked at:
-updating statistics
-checking the execution plan
-DBCC showcontig
View 5 Replies
View Related
Jun 13, 2002
Hi !
In the older versions 6.5-7.0 you could adjust max_async_io setting if you had fast controllers and disks that could handle more IO from SQLServer. In 2000 this setting is removed. Are there any settings that I could adjust/tune regarding IO and disk handling ?
Regards Mattias
View 1 Replies
View Related
May 17, 2007
What all possible ways there have been to maximizing performance of database?
View 4 Replies
View Related
Aug 2, 2007
Hello-
Can some one give links to SQL Server tuning and applications performace.
I am looking for some examples which i can use in interviews .
thanks.
View 1 Replies
View Related
Aug 23, 2007
how to increase performance through management studio
except making indexes
View 4 Replies
View Related
Mar 7, 2008
Hi Guys I need someone to assist me in having full understanding of what performance turning is. My Challenge is interpreting the System Monitor Graph of Performance tools.
I needed to know what does the value on vertical axis represent, while there is non on the horizontal axis rather I have Last, Average, Minimum, Maximum and Duration; Please What does all this value stands for, I indeed observe that when I click on any of the counters selected the value changes, therefore kindly assist me so that I can make meaning of this. Apart from all this please assist me with any material that can explain performance tuning to my maximum benefit, thanks in anticipation.
Pls Check below sample of the file is attached
View 1 Replies
View Related
May 28, 2008
Hi,
Mine Below Query takes considerable time at the time of execution.
Can any one help me, what is the other way to write this query?
Declare @p_Mkt_View_Id int
Set @p_Mkt_View_Id = 17
Select Distinct Customer_id From Active_Product_Cust_Dtl
Where Product_Group_Code in
(Select Distinct Product_Group_Code From Products
Where Product_code in (
select Distinct ProductId from pit
where pitid in (select pitid from marketviewdef
where mktviewid = @p_Mkt_View_Id)))
Thanks
Prashant
View 4 Replies
View Related
Jun 11, 2008
Hi All,
I am new to this forum, would like to know about performance tuning methods in sql and which is the best site to read about it?
Thanks
View 5 Replies
View Related
Mar 13, 2007
Hi experts,
I've run sql profiler with %processor time counter .it showed a large value of 75
so please give me steps to tune the long running query with high cpu utilisation.
thanks in advance
View 20 Replies
View Related
Jul 26, 2007
for what purpose we are splitting the non-clustered index into 3 instead of 1
create index si_acct_info_dtl_INDX1 on si_acct_info_dtl(account_code, ctrl_acct_type)
create index si_doc_hdrfk_ci_acct_info_dtl on si_acct_info_dtl(tran_ou, tran_type, tran_no)
whether index rebuit everycolumn when search is given????.if we build the index in one
statement like this:create index si_acct on si_acct_info_dtl(batch_id)
create index si_acct_info_dtl_guid on si_acct_info_dtl(batch_id,account_code, ctrl_acct_type,tran_ou, tran_type, tran_no)
View 4 Replies
View Related
Aug 23, 2007
i just wanna know how to reduce the performance of the query
can any one pls help me in the gaining the performance of query
SELECT
tblBankruptcyInfo.MasterID,
tblBankruptcyInfo.bk_Case_Number
INTO #ActiveBK
FROM FNFBSDataMart.dbo.tblReferral tblReferral WITH (NOLOCK)
INNER JOIN FNFBSDataMart.dbo.tblBankruptcyInfo tblBankruptcyInfo WITH (NOLOCK)
ON tblReferral.RefID = tblBankruptcyInfo.RefID
AND tblReferral.CloseDate IS NULL
INNER JOIN FNFBSDataMart.dbo.tblSuperClientFile tblSuperClientFile WITH (NOLOCK)
ON tblReferral.ClientFileID = tblSuperClientFile.ClientFileID
AND tblSuperClientFile.SuperClientVendorID IN (1816,125,127,1706,766,1820,137,141,144,145,1593,1808,146,990,1745,149,1215,1854,1867)
GROUP BY
tblBankruptcyInfo.MasterID,
tblBankruptcyInfo.bk_Case_Number
View 6 Replies
View Related
Nov 19, 2007
Hi all ,
I am doing performance tuning in sql
I have a query which it gives result in 70000 rows and time is taken 7 mints .
But i want one query is not more than 50000 row
my query is :-
SELECT
QPDMADM.LAR_OMEGA_TRANS_SUM.SOURCE_TRANSACTION_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.ORDER_DATE,
QPDMADM.LAR_OMEGA_TRANS_SUM.SHIP_DATE,
QPDMADM.LAR_OMEGA_TRANS_SUM.FISCAL_PERIOD,
QPDMADM.LAR_OMEGA_TRANS_SUM.DISCOUNT_AGREEMENT_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.PRODUCT_ID_07,
QPDMADM.LAR_OMEGA_TRANS_SUM.CUSTOMER_ID_14,
QPDMADM.LAR_OMEGA_TRANS_SUM.ISO_COUNTRY_CODE2,
QPDMADM.LAR_OMEGA_TRANS_SUM.ACCOUNT_TYPE_CODE,
QPDMADM.LAR_OMEGA_TRANS_SUM.END_USER_CUSTOMER_ID,
sum(QPDMADM.LAR_OMEGA_TRANS_SUM.NIA_AMT),
QPDMADM.LAR_OMEGA_TRANS_SUM.INDUSTRY_CLUSTER_CODE,
QPDMADM.LAR_OMEGA_TRANS_SUM.SOURCE_SYSTEM_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.LOAD_DATE
FROM
QPDMADM.LAR_OMEGA_TRANS_SUM
WHERE
(
QPDMADM.LAR_OMEGA_TRANS_SUM.FISCAL_PERIOD = '200712'
)
GROUP BY
QPDMADM.LAR_OMEGA_TRANS_SUM.SOURCE_TRANSACTION_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.ORDER_DATE,
QPDMADM.LAR_OMEGA_TRANS_SUM.SHIP_DATE,
QPDMADM.LAR_OMEGA_TRANS_SUM.FISCAL_PERIOD,
QPDMADM.LAR_OMEGA_TRANS_SUM.DISCOUNT_AGREEMENT_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.PRODUCT_ID_07,
QPDMADM.LAR_OMEGA_TRANS_SUM.CUSTOMER_ID_14,
QPDMADM.LAR_OMEGA_TRANS_SUM.ISO_COUNTRY_CODE2,
QPDMADM.LAR_OMEGA_TRANS_SUM.ACCOUNT_TYPE_CODE,
QPDMADM.LAR_OMEGA_TRANS_SUM.END_USER_CUSTOMER_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.INDUSTRY_CLUSTER_CODE,
QPDMADM.LAR_OMEGA_TRANS_SUM.SOURCE_SYSTEM_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.LOAD_DATE
Note:- FISCAL_PERIOD is index only
Thank & Regards,
Anil
View 2 Replies
View Related
Jul 23, 2005
hi!!!the following is the sql which is veri slow cos of the 'Not In' clause,would appreciate if u anyone can suggest any other way to bring aboutthe same resultSELECT Id, LOC, AGENCY, BATCHFROM tblRowsWHERE tblRows.AGENCY NOT IN (SELECT DISTINCT B.AGENCYFROM tblRows AS A, tblRows AS BWHERE A.LOC = "B"AND B.AGENCY = A.AGENCYAND B.BATCH = A.BATCH)tblRowsID LOC AGENCY BATCH1 B 1000 WAD2 R 1000 WAD3 B 1010 QAD4 B 1020 WAD5 R 1020 WAD6 R 1030 RRR7 I 1030 RRR8 V 1030 RRR9 B 1040 UIA10 R 1040 UIA11 I 1040 UIA12 V 1040 UIAthe subquery is to return the rows with LOC = B. the above query as thewhole should return the rows where LOC <> b and also must exclude rowsbelong to the LOC = B subset (that is for example the first two rowswith ID 1 and 2. the first row with ID 1 has LOC as B, Agency as 1000and BATCH as WAD. the second row with Id 2 has LOC as R as the sameAGENCY and BATCH as first row with ID 1 so is the subset of first row.similarly row with Id's 4 and 5.the above query must return the following the rows (that is we can saythe orphan rows which doesn't have LOC AS B nor belongs to the B'ssubset6 R 1030 RRR7 I 1030 RRR8 V 1030 RRRhope i am clear in my explanation and would appreciate if someone canpoint me in the right direction. the reason for posting this in msaccess group is because this is going to be a query in MS Access.regardsbala
View 7 Replies
View Related
Nov 23, 2005
Hi Guru,My company, every thing we need is to buy from a vendor. However, wehave PEOPLESOFT CRM app that has around 6000 tables and around 5000views and none stored proc. We start seeing the slowness of the app.When I started running a trace to capture some data, and there is nouseful info other than built-in API sp cursors in the following below:sp_cursoropensp_cursorfetchsp_cursorsp_cursorcloseWhat is the best way to capture SQL statements when I see very highREADS? If I tell the peoplesoft prorammer guy to capture SQL statementsfrom application, will he be able to do it?I just need the SQL statements to look at execution plan before I throwout some useful indexes.Please help!Thanks so much,Silaphet,
View 1 Replies
View Related
Oct 25, 2007
Hi have this query which is taking too much time to execute..........I have tried the following options but not useful till now
1. NOLOCK
2. SET NO ROW COUNT
3. CHANGED DISK LOCATION OF TEMPDB
4. CHECKED %Processor Time
5. Checked pages.sec
Below is the query, any suggestions will be really helpful
SE IFRepository
--Query
--Returns count of txns whose status is not (10001 or 10002)
declare @fileruntimeuid int
declare @Pendingackcount int
set @Pendingackcount =0
set @fileruntimeuid =0
declare @clientname varchar(256)
set @clientname = NULL
declare @txncnt int
set @txncnt = 0
declare @FileNameClient varchar(256)
set @FileNameClient = NULL
declare @StageStatus int
set @StageStatus = 0
declare @StageDesc varchar(35)
declare PendingAcks cursor for
select distinct fileruntimeuid from tiffileruntime WITH (NOLOCK)
where filecreationdt >= convert(smalldatetime,'9-11-07')
and filecreationdt <= convert(smalldatetime,'9-12-07')
and statusid <> 2
--and filetypeuid in (1,8,16,17,18)
--and clientuid =1205
order by fileruntimeuid
--244873, 244883, 244885, 244892, 244893, 244925, 244926, 244966, 244967, 244873, 244883
Print 'File Life Cycle Viewer via Database'
Print '==========================================================================================='
Print 'Status FileRuntimeUID Client Name File Status File name'
Print '==========================================================================================='
Open PendingAcks
FETCH NEXT FROM PendingAcks
into @fileruntimeuid
WHILE @@FETCH_STATUS = 0
BEGIN
select top 1 @StageDesc = b.IFComponentDesc from TIFComponent b, TIFFIleProcessingStatus a
WITH (NOLOCK)
where a.IFComponentUID = b.IFComponentuid
and a.fileruntimeuid = @fileruntimeuid
order by a.FPROCStageStartDt desc-- a.IFComponentUID desc
select @clientname = ClientShortName from tifclientattrib
where clientuid = (select clientuid from tiffileruntime where fileruntimeuid = @fileruntimeuid)
select @StageStatus = statusid, @FileNameClient= FileNameClient from tiffileruntime where fileruntimeuid = @fileruntimeuid
select @txncnt = FProcTxnProcessedInTotal from tiffileprocessingstatus where fileruntimeuid = @fileruntimeuid and IFComponentUID = 5
--if @StageDesc = "" Begin @StageDesc = "------------" End
print RTRIM(convert(varchar(10),@StageStatus)) + ' ' + RTRIM(convert(varchar(10),@fileruntimeuid)) + ' ' + RTRIM(@clientname) + ' ' + @StageDesc + ' ' + RTRIM(@FileNameClient)
set @StageDesc = NULL
FETCH NEXT FROM PendingAcks
into @fileruntimeuid
END
Print '==========================================================================================='
close PendingAcks
deallocate PendingAcks
View 5 Replies
View Related
Aug 2, 2007
This cursor is processing one row per second, all it is doing is appending a value to a column. There are 847,000 rows and I can't leave the query to run for 10 days! help!
CREATE procedure [dbo].sp_USR_TEMP_SESSIONAL_ATTENDANCE_APPENDasbegin DECLARE @I_UPN varchar(13) DECLARE @I_SURNAME varchar(50) DECLARE @I_FORENAME varchar(50) DECLARE @I_DOB datetime DECLARE @I_GENDER varchar(1) DECLARE @I_LEA varchar(3) DECLARE @I_DFES varchar(4) DECLARE @I_ATTEND_YEAR varchar(4) DECLARE @I_WEEK_BEGINNING datetime DECLARE @I_ATTEND_CODES varchar(14)declare @cnt intset nocount ondeclare cur CURSORfor select upn, surname, forename, dob, gender, '353' as lea, dfes, attend_year, week_beginning, attend_codes from tmpATTEND_IMPORTfor read onlyopen curfetch from cur into @I_UPN, @I_SURNAME, @I_FORENAME, @I_DOB, @I_GENDER, @I_LEA, @I_DFES, @I_ATTEND_YEAR, @I_WEEK_BEGINNING, @I_ATTEND_CODEStruncate table tmpATTEND_IMPORT_APPENDEDwhile @@fetch_status = 0 begin set @cnt = (select count(*) as cnt from tmpATTEND_IMPORT_APPENDED where upn = @I_UPN and surname = @I_SURNAME and forename = @I_FORENAME and dob = @I_DOB and gender = @I_GENDER and lea = @I_LEA and dfes = @I_DFES and attend_year = @I_ATTEND_YEAR)if @cnt = 0 insert tmpATTEND_IMPORT_APPENDED( upn, surname, forename, dob, gender, lea, dfes, attend_year, week_beginning, attend_codes) values ( @I_UPN, @I_SURNAME, @I_FORENAME, @I_DOB, @I_GENDER, @I_LEA, @I_DFES, @I_ATTEND_YEAR, @I_WEEK_BEGINNING, @I_ATTEND_CODES)else update tmpATTEND_IMPORT_APPENDED set attend_codes = attend_codes+@I_ATTEND_CODES where upn = @I_UPN and surname = @I_SURNAME and forename = @I_FORENAME and dob = @I_DOB and gender = @I_GENDER and lea = @I_LEA and dfes = @I_DFES and attend_year = @I_ATTEND_YEARfetch next from cur into @I_UPN, @I_SURNAME, @I_FORENAME, @I_DOB, @I_GENDER, @I_LEA, @I_DFES, @I_ATTEND_YEAR, @I_WEEK_BEGINNING, @I_ATTEND_CODESendclose curdeallocate curendGO
View 1 Replies
View Related
May 22, 2006
I want to take the execution plan of some transact sql queries, I took the execution plan as text based one, that having
index scan, index seek
Remote scan, remote Update
sort order by cluases
in the above clauses what is the high performance, and how will i change to the high performance clauses by changin the query to improve the execution speed of the query
Please guide me
the execution plan is
SELECT [Inventory_Profile].[InventoryID] ,[Inventory_Profile].[Alias] ,[Inventory_Profile].[InventoryStatusID] ,[Inventory_Profile].[InventorySubTypeID] ,[Inventory_Profile].[InventoryTypeID] ,[Inventory_Profile].[AcquisitionDate] ,[Inventory_Profile].[UnitNumber] ,[Inventory_Profile].[YearOfManufacture] ,[Inventory_Profile].[Manufacturer] ,[Inventory_Profile].[Make] ,[Inventory_Profile].[Model] ,[Inventory_Profile].[SerialNumber] ,[Inventory_Profile].[UsageConditionID] ,[Inventory_Profile].[Description1] ,[Inventory_Profile].[Description2] ,[Inventory_Profile].[LocationEffectiveFromDate] ,[Inventory_Profile].[IsFlaggedForSale] ,[Inventory_Profile].[RentalPurchaseOrderNumber] ,[Inventory_Profile].[AquisitionPurchaseOrderNumber] ,[Inventory_Profile].[SortOrder] ,[Inventory_Profile].[IsSaleLeaseBack] ,[Inventory_Profile].[InterimRentReceivableUpfrontTaxModeID] ,[Inventory_Profile].[LeaseRentalReceivableUpfrontTaxModeID] ,[Inventory_Profile].[OverTermReceivableUpfrontTaxModeID] ,[TaxDepreciation_Inventory].[IsTaxDepreciationRequired] ,[TaxDepreciation_Inventory].[IsComputationPending] ,[TaxDepreciation_Inventory].[TaxDepreciationTemplateID] ,[TaxDepreciation_Inventory].[InventoryCostBasisAmount] ,[TaxDepreciation_Inventory].[DepreciationBeginDate] ,[TaxDepreciation_Inventory].[DepreciationEndDate] ,[TaxDepreciation_Inventory].[IsTaxDepreciationTerminated] ,[TaxDepreciation_Inventory].[IsStraightLineMethodUsed] ,[TaxDepreciation_Inventory].[IsLeaseTermUsedForStraightLineMethod] ,[Inventory_PTMS].[Division] ,[Inventory_PTMS].[Branch] ,[Inventory_PTMS].[SalesTaxPercent] ,[Inventory_PTMS].[SalesTaxAmount] ,[Inventory_PTMS].[IsSalesTaxIncluded] ,[Inventory_PTMS].[GLExpenseAccount] ,[Inventory_PTMS].[GLAssetAccount] ,[Inventory_PTMS].[SoftwareExclusionAmount] ,[Inventory_PTMS].[AssetCategoryCodeID] ,[Inventory_PTMS].[OwnershipCodeID] ,[Inventory_PTMS].[ManufacturingCodeID] ,[Inventory_PTMS].[ReimburseCodeID] ,[Inventory_PTMS].[BillingStatusID] ,[Inventory_PTMS].[PropertyTaxExemptionCodeID] ,[Inventory_PTMS].[UserDefinedField1] ,[Inventory_PTMS].[UserDefinedField2] ,[Inventory_PTMS].[Notes] FROM [Inventory_Profile] INNER JOIN [TaxDepreciation_Inventory] ON [Inventory_Profile].[InventoryID]=[TaxDepreciation_Inventory].[InventoryID] INNER JOIN [Inventory_PTMS] ON [Inventory_Profile].[InventoryID]=[Inventory_PTMS].[InventoryID] INNER JOIN [Inventory_Status_CnfgLocale] ON [Inventory_Profile].[InventoryStatusID] in (SELECT InventoryStatusID FROM Inventory_Status_CnfgLocale WHERE InventoryStatusName <> 'Donated' and InventoryStatusName <> 'Scrap' and InventoryStatusName <>'Write Off' and InventoryStatusName <> 'Sold')
|--Nested Loops(Inner Join)
|--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]))
| | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_Profile].[InventoryStatusID]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]))
| | | | |--Clustered Index Scan(OBJECT:([LW_Winthrop].[dbo].[Inventory_PTMS].[PK_IInvPTMS]))
| | | | |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[Inventory_Profile].[PK_IInventory_InvID]), SEEK:([LW_Winthrop].[dbo].[Inventory_Profile].[InventoryID]=[LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[PK_cdInvStatus_Locale]), SEEK:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusID]=[LW_Winthrop].[dbo].[Inventory_Profile].[InventoryStatusID]), WHERE:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Donated' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Scrap' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Sold' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Write Off') ORDERED FORWARD)
| | |--Index Seek(OBJECT:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[UK_ITaxDepInfo]), SEEK:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryID]=[LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[PK_ITaxDepInfo]), SEEK:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]=[LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]) LOOKUP ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[PK_cdInvStatus_Locale]))
View 6 Replies
View Related
Feb 14, 2008
On an average system how long should SQL Server take to retrun 300,000 rows from an average table with Select * From? Milliseconds? Seconds? Minutes?
Thanks.
View 16 Replies
View Related
Apr 22, 2008
Hi,
Our reporting tool dynamically creates tables and fields based upon Excel spreadsheets that are imported. To improve query performance, we are attempting to create indices on these tables automatically. The problem we are facing is that the tool allows the user to group the data by a whole range of fields when generating reports, therefore making it hard to decide what indices to create.
So, as an example, we have a table that has 10 VARCHAR(250) fields (the default size for text based fields in the tool), each of which could potentially be in the group by clause, and 15 numeric fields on which calculations will be carried out.
Is there a single index that we can create dynamically that would improve performance for all potential queries that may be generated? Such as an index that contains all the 10 VARCHAR(250) fields, and the numeric values as Included Columns?
If so, what effect does the order of the fields in the index have, ie we have some fields that have a lot of distinct values and some that have only a few. Which ones should appear higher in the list?
Any help would be greatly appreciated.
James
View 5 Replies
View Related
