Slow Query On Primary Key Using LEFT
Dec 19, 2001
My table consists of about 1.4 Million Records. The PK is a CHAR field and ranges in size from 3 - 25 characters. I need to pull a recordset using the LEFT function.
Example: SELECT blah WHERE LEFT(myPK, 8) = 'AIRBILLNU'
This query takes about 115927ms to run and the server is 100% CPU bound, it should only bring up 2 records. Seems like the index is not being used. I know the DB design is probably not the greatest, we probably should have had an INT PK and IX on the other field which is now the PK. I cannot do anything about that at this point.
Is there anything I can do to speed up this query.?
Thanks,Adrian
View 3 Replies
ADVERTISEMENT
Apr 21, 2006
Here's a little background on the query. I have a list of documents by an id number in one table and the description of the sheets in another table. It's a one to many relationship, so for each description, there may be multiple entries in the documents table that it applies to. For example:
Descriptions table:
ID | Title
Doc1 | Document 1
Doc2 | Document 2
Documents table:
ID | Parent
Doc1 | 10400
Doc2 | 10400
Doc1 | 20189
Doc3 | 20189
View:
ID | Parent | Description
Doc1 | 10400 | Document 1
Doc2 | 10400 | Document 2
Doc1 | 20189 | Document 1
Doc3 | 20189 | (null)
So the query I am using uses a left join to combine the data from the one table into the other. There might not be an entry for the description, so for some Document entries, the description field may be blank. For some reason, certain queries take about 2 minutes longer than others who retreive 5 times the information.
In SQL Manager, is says "Executing Query. Waiting for response from data source." After about 20 seconds it says "Retrieving Data..." then about a minute later, it finally comes up with the data. I can select another parent that has a lot more items and it comes up in about 3 seconds max.
It's running on SQL Server 2005 with 2GB of RAM.
Any suggestions on tracking down the reason for the slowness would be great.
Thanks in advance!!!
-Dan
View 8 Replies
View Related
Oct 28, 2007
Hi,
I'm executing a nested queries consisting of LEFT OUTER JOIN for:
Duration = 27 sec;
Reads = 1690;
Number of users = 1.
This is extremely slow for a small set of data. I'm afraid that when we have a larger dataset and more concurrent users that query will take forward.
What am I doing wrong for my query to be taking too long?
Here's the query:
SELECT B.Business_Id as Business1_0_,
B.Place_Of_Business_Id as Place2_30_0_,
B.Business_Type_Id as Business3_30_0_,
B.Business_Name as Business4_30_0_,
B.Business_Description as Business5_30_0_,
B.Last_Update_Timestamp as Last6_30_0_
FROM Busines as B
LEFT OUTER JOIN Business_Service as BS
ON B.Business_Id = BS.Business_Id
LEFT OUTER JOIN Business_Service_Category as BSC
ON B.Business_Id = BSC.Business_Id
LEFT OUTER JOIN Business_Sub_Category as BSSC
ON B.Business_Id = BSSC.Business_Id
where B.Business_Id IN (
Select B.Business_Id from Busines as B
JOIN Business_Address as BA ON B.Business_Id = BA.Business_Id
JOIN Address as A ON A.Address_Id = BA.Address_Id
WHERE A.City_Name like '%New York%'
and A.state = 'NY') AND ( FREETEXT(B.Business_Name, 'Designer')
or BSC.Service_Category_Id IN
( Select SC.Service_Category_Id from Service_Category as SC
where FREETEXT(SC.Service_Category_Name, 'Designer') )
or BSSC.Sub_Category_Id IN (
Select SSC.Service_Sub_Category_Id from Service_Sub_Category as SSC
where FREETEXT(SSC.Service_Sub_Category_Name, 'Designer') )
or BS.Service_Id IN (
Select S.Service_Id from Service as S
where FREETEXT(S.Service_Name, 'Designer') ) )
----------------------------------------------------------------------------------------------------------------------------------------
Here's the SQL Query Plan:
|--Nested Loops(Left Semi Join, OUTER REFERENCES.[Business_Id], [BS].[Service_Id], [BSC].[Service_Category_Id], [BSSC].[Sub_Category_Id]))
|--Nested Loops(Left Outer Join, WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]=[PB].[dbo].[Business_Sub_Category].[Business_Id] as [BSSC].[Business_Id]))
| |--Nested Loops(Left Outer Join, WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]=[PB].[dbo].[Business_Service_Category].[Business_Id] as [BSC].[Business_Id]))
| | |--Nested Loops(Left Outer Join, WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]=[PB].[dbo].[Business_Service].[Business_Id] as [BS].[Business_Id]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES.[Business_Id]))
| | | | |--Sort(DISTINCT ORDER BY.[Business_Id] ASC))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES[BA].[Business_Id]))
| | | | | |--Hash Match(Inner Join, HASH.[Address_Id])=([BA].[Address_Id]), RESIDUAL[PB].[dbo].[Address].[Address_Id] as .[Address_Id]=[PB].[dbo].[Business_Address].[Address_Id] as [BA].[Address_Id]))
| | | | | | |--Clustered Index Scan(OBJECT[PB].[dbo].[Address].[PK__Address__0519C6AF] AS ), WHERE[PB].[dbo].[Address].[State] as .[State]='NY' AND [PB].[dbo].[Address].[City_Name] as .[City_Name] like '%JayVille%'))
| | | | | | |--Index Scan(OBJECT[PB].[dbo].[Business_Address].[ClusteredIDX_Business_Address] AS [BA]))
| | | | | |--Index Seek(OBJECT[PB].[dbo].[Busines].[UI_ukBusiness] AS ), SEEK.[Business_Id]=[PB].[dbo].[Business_Address].[Business_Id] as [BA].[Business_Id]) ORDERED FORWARD)
| | | | |--Clustered Index Seek(OBJECT[PB].[dbo].[Busines].[PK_Busines] AS ), SEEK.[Business_Id]=[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]) ORDERED FORWARD)
| | | |--Clustered Index Scan(OBJECT[PB].[dbo].[Business_Service].[ClusterIDX_Business_Service] AS [BS]))
| | |--Clustered Index Scan(OBJECT[PB].[dbo].[Business_Service_Category].[ClusterIDX_Business_Service_Category] AS [BSC]))
| |--Clustered Index Scan(OBJECT[PB].[dbo].[Business_Sub_Category].[ClusterIDX_Business_Sub_Category] AS [BSSC]))
|--Concatenation
|--Filter(WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id] = [Full-text Search Engine].[KEY]))
| |--Remote Scan(OBJECTFREETEXT))
|--Nested Loops(Left Semi Join)
| |--Clustered Index Seek(OBJECT[PB].[dbo].[Service_Category].[PK__Service_Category__15502E78] AS [SC]), SEEK[SC].[Service_Category_Id]=[PB].[dbo].[Business_Service_Category].[Service_Category_Id] as [BSC].[Service_Category_Id]) ORDERED FORWARD)
| |--Filter(WHERE[Full-text Search Engine].[KEY] = [PB].[dbo].[Business_Service_Category].[Service_Category_Id] as [BSC].[Service_Category_Id]))
| |--Remote Scan(OBJECTFREETEXT))
|--Nested Loops(Left Semi Join)
| |--Clustered Index Seek(OBJECT[PB].[dbo].[Service_Sub_Category].[PK_Service_Sub_Category] AS [SSC]), SEEK[SSC].[Service_Sub_Category_Id]=[PB].[dbo].[Business_Sub_Category].[Sub_Category_Id] as [BSSC].[Sub_Category_Id]) ORDERED FORWARD)
| |--Filter(WHERE[Full-text Search Engine].[KEY] = [PB].[dbo].[Business_Sub_Category].[Sub_Category_Id] as [BSSC].[Sub_Category_Id]))
| |--Remote Scan(OBJECTFREETEXT))
|--Nested Loops(Left Semi Join)
|--Clustered Index Seek(OBJECT[PB].[dbo].[Service].[PK__Service__117F9D94] AS ), SEEK.[Service_Id]=[PB].[dbo].[Business_Service].[Service_Id] as [BS].[Service_Id]) ORDERED FORWARD)
|--Filter(WHERE[Full-text Search Engine].[KEY] = [PB].[dbo].[Business_Service].[Service_Id] as [BS].[Service_Id]))
|--Remote Scan(OBJECTFREETEXT))
Please Help....
View 2 Replies
View Related
Sep 1, 2014
Looking to improve performance of the following code.
It basically generates future days for each dog. So there is a dog table and a day table with every day.
These 2 table cross join and then fill in missing rows. As time moves i will fill in further future dates but will need the initial insert to be a reasonable query.
All columns are covered by index's but the queries at the end take quite a long time. I would hope for index scan to just point out the missing rows especially on the final query.
How to make the last query as fast as possible.
IF OBJECT_ID('dbo.[AllDates]', 'U') IS NOT NULL
DROP TABLE dbo.[AllDates]
CREATE TABLE dbo.[AllDates] (
[Date] date not null PRIMARY KEY
)
;WITH Dates AS
[Code] .....
View 2 Replies
View Related
Jul 23, 2005
I've been discussing here a SQL 7 view which scrolls slowly when linked toan Access 2000 MDB. After trying various things, I've distilled it down tothe following: when the linked view has a virtual primary key, it is slow;when there is no virtual primary key, it scrolls quickly.Looking at Profiler, I see that when there is no virtual primary key, Accesssends a simple select command to SQL Server. However, when there is avirtual primary key, Access gets a list of primary key values, and then usesthose values to get records from SQL Server in groups of 10.This may account for why the scrolling is slow when there is a virtualprimary key. However, I wonder if there's any way to improve performance orotherwise work around this apparent bottleneck.The MS literature says that creating a virtual primary key will not affectperformance. However, since different methodologies for obtaining data areused with and without the virtual primary key, it seems that it does affectperformance, and certainly does in this situation.Thanks,Neil
View 9 Replies
View Related
Jun 29, 2005
:confused:
Hello
i have some problem with this MDX Statment :
with
SET [kunden] as 'Filter (descendants([Dim_x].[Alle Kunden], , LEAVES),
left([Dim_x],1,1) = "(" ) '
select
{descendants([Dim_Kx].[Alle Kunden], , LEAVES) } on rows,
{([measures].[Standard])} on columns
from z
Result :
Standard
(test1) 44444
(test2) 54567
(test3) 3214
test5 4535
test6 11111
but i want to filter i need only result with () how can i do it please
i need help
many thanks,
Prince
View 1 Replies
View Related
Jul 23, 2005
I am able to run a query which runs FAst in QA but slow in theapplication.It takes about 16 m in QA but 1000 ms on theApplication.What I wanted to know is why would the query take a longtime in the application when it runs fast on SQL server?How should we try debugging it?Ajay
View 2 Replies
View Related
Dec 10, 2007
I have this database running (ignore that the ERD below was done in Access, this is being made in Microsoft SQL Server 2005).
What I need to do is if you look at the users table and the orderContents table I need to make a query that:
Collects the users' names and any products they have bought (preferably shown by title not ID) as well as still showing the users that have not ordered any products.
I get the feeling there's a left join involved, but can't quite see how to do it.
Thank you in advanced for any help.
View 3 Replies
View Related
Sep 21, 2007
Hello,
I have a query with 11 left joins. Some hits against tables with small amounts of reference data, whereas others are not so small. Should I rewrite this in another way, as performance is a requirement on this one? Or, should I do it another way?
How would I rewrite left joins? Any examples?
Thanks.
View 5 Replies
View Related
Sep 12, 2006
Hello all,
I am stuck in a bit of a mess, and i really don't know how to get out of it.
I am a dot.net developer, working with MS SQL server 2000.
I am trying to write a query which essentially gives the amount of stock available for a particular stock_code.
select Stock_code, description from stock
Now what i want to do is, for each one of the stock_code that apears in the table above i want to look into another table called pop_item, and get the closest delivery_date for that particular stock_code. So i have top 1 and order it by delivery_date desc.
Individually for a particular stock_code, i can do this no problem. And here is my code for stock_code='E0016/6'
select top 1
stock_code, delivery_date, order_number,qty_order-qty_delivered as onorder
from pop_item
where
stock_code='E0016/6' and
qty_order>qty_delivered
order by delivery_date desc
But I can't seem to be able to do this for all the stock_code, and not a specific one, cause even though i try and left outer join it, i can't access the outer stock_code from the first query into the next...
i.e
select stock.Stock_code, description, tempp.stock_code, tempp.delivery_date, tempp.onorder from stock
left outer join
(select top 1
stock_code, delivery_date, order_number,qty_order-qty_delivered as onorder
from pop_item
where
--Can't say this(stock_code= stock.stock_code and )
qty_order>qty_delivered
order by delivery_date desc) as tempp
on tempp.stock_code=stock.stock_code
Now my question is, is there anyway to access stock.stock_code within the second query? Casue the whole query on top returns only one value for delivery_date, only of the highest delivery date in the whole of pop_item. which make sense... but i don;t know how to get around this...
OOOOOOOOOOOOOOOOOOhhhhhhhhhhhhhhhhhhhhhhhhhhhhh!
Hope someone can help me.
Regards,
Munira
ps- should i be using a cursor, can i call cursors from asp.net. every where i read about cursors they adivice us not to use them.
View 8 Replies
View Related
Jul 24, 2012
I'm trying to write a 3 table query using two LEFT JOINs. Originally, I only had one LEFT JOIN and prior to the addition of the the third table (parts) this query worked. Now it doesn't. I think it has to do with my GROUP BY.
SELECT
quote.quote_id, parts.material, machining_operations.machine, machining_operations.per_roughing, machining_operations.per_of_machining,
machining_operations.programming_time, machining_operations.setup_time, machining_operations.cycle_time, machining_operations.notes
quote.part_name, quote.revision_no, quote.quantity, quote.initial_volume, quote.final_volume, quote.material_price, machining_operations.mo_id
FROM quote
LEFT JOIN machining_operations
ON machining_operations.quote_num = quote.quote_id
LEFT JOIN parts
ON parts.package_no = quote.package_no AND parts.part_name = quote.part_name
GROUP BY quote.quote_id
View 10 Replies
View Related
May 8, 2008
I have been working on this for a little bit and have gotten to this point. Below is the query in blue, the error in red. Now, from what I gather the error is telling me I can't insert a duplicate product code into tblProduct. Isn't that what the left join is exactly not doing, as in, the left join is inserting all records from Complete_records into tblProduct where the code is null(does not exist) in tblProduct? Or, is this an issue where the identity number, productID in tblProduct, isn't starting at the next number in turn?
Thanks.
Set Identity_Insert tblProduct on
DECLARE @MaxId int
SELECT @MaxID=MAX(productID)
FROM tblProduct
SELECT IDENTITY(int,1,1) AS ID,Complete_products.APNum, Complete_products.Title, Complete_products.CategoryID, Complete_products.Mountable, Complete_products.price,
Complete_products.Height, Complete_products.Width, Complete_products.IRank, Complete_products.frameable, Complete_products.Typ INTO #Temp
FROM Complete_products LEFT OUTER JOIN
tblProduct AS tblProduct_1 ON Complete_products.APNum = tblProduct_1.productCode
where tblProduct_1.productCode IS NULL
INSERT INTO tblProduct
(productID,productCode, productName, productNavID, CanBeMounted, productRetailPrice, productHeight, productWidth, Rank, CanBeFramed, ProductType)
SELECT @MaxID + ID, APNum, Title, CategoryID, Mountable, price,
Height, Width, IRank, frameable, Typ
FROM #Temp
(236752 row(s) affected)
Msg 2601, Level 14, State 1, Line 13
Cannot insert duplicate key row in object 'dbo.tblProduct' with unique index 'IX_tblProductt_productCode'.
The statement has been terminated.
View 18 Replies
View Related
Dec 12, 2007
How to use two left outer join in a single query?
I was trying to run the follwoing query.It is giving me error
"select woancestor.wonum,wplabor.laborcode, wplabor.quantity,wplabor.laborhrs
from wplabor,workorder left outer join woancestor on workorder.wonum=woancestor.wonum
and wplabor left outer join labtrans on wplabor.laborcode=labtrans.laborcode
where woancestor.ancestor='572099' and workorder.istask=1
and workorder.wonum=wplabor.wonum"
Error is "Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'left'."
But the following query works fine
"select woancestor.wonum,wplabor.laborcode, wplabor.quantity,wplabor.laborhrs
from wplabor,workorder left outer join woancestor on workorder.wonum=woancestor.wonum
where woancestor.ancestor='572099' and workorder.istask=1
and workorder.wonum=wplabor.wonum"
please help me
View 2 Replies
View Related
Oct 28, 2015
I have a table PLACE with a character column CODE
[Code] [nchar](4) NULL
I need to left pad the column with 0 if it is less than 4 characters long and extract the first 2 characters on the left into a new column COUNTY_CODE.
How can I do that in transact SQL?
I tried:
SELECT RIGHT(RTRIM('0000'+ISNULL([Code],'')),4)
FROM [Place]
WHERE [Place Code]='B' and [Code]='627'
And I got 0627. And how do I extract the first 2 characters?
View 10 Replies
View Related
Mar 14, 2008
I am having an issue with large queries using Microsoft SQL Server 2005 - 9.00.2221.00 (X64).
I have a query with many INNER/LEFT OUTER/RIGHT OUTER joins which is taking very very very long to run. This looks exactly like this problem described in http://support.microsoft.com/kb/318530. However, this doc says it was fixed in SP1, which is already installed.
Basically I have a query:
SELECT ....
FROM TABLEA
INNER JOIN TABLEB ...
LEFT OUTER TABLEC...
LEFT OUTER TABLED...
RIGHT OUTER TABLEF...
LEFT OUTER TABLEJ..
LEFT OUTER TABLEH...
LEFT OUTER TABLEI...
RIGHT OUTER TABLEK...
LEFT OUTER TABLEM..
... 17 joined tables in all......
WHERE TABLEB.field1 = 'abc'
The query plan for this is using TABLEA as the "main" table and joining everything else to it. The problem is, TABLEA has 117 MILLION records. TABLEB has 10,000 records which match the WHERE. I stopped this query after it ran for 62 HOURS.
If I simply change the query to:
SELECT ....
FROM TABLEB
INNER JOIN TABLEA ...
LEFT OUTER TABLEC...
LEFT OUTER TABLED...
RIGHT OUTER TABLEF...
LEFT OUTER TABLEJ..
LEFT OUTER TABLEH...
LEFT OUTER TABLEI...
RIGHT OUTER TABLEK...
LEFT OUTER TABLEM..
... 17 joined tables in all......
WHERE TABLEB.field1 = 'abc'
The query runs in 15 mins. The query plan now uses TABLEB and the WHERE clause to join all the other tables.
The problem is, this query is generated from a report writter, and I have no control over the way it creates the SQL code.
Any ideas how I can fix this?
View 1 Replies
View Related
Nov 19, 2004
I have an update query running which to just now has been running for 22 hours running on two tables 1 a lookuptable that has just been created within the batch the other a denormalised table for doing data analysis on
the query thats causing teh problem is
--//////////////////////////////////// this is the one thats running
Print 'Update Provider 04-05 EmAdmsCount12mths : ' + CAST(GETDATE() AS varchar)
GO
Update Provider_APC_2004_05
set EmAdmsCount12mths =
(Select COUNT(*)-1
from Combined_Admissions
where ((Combined_Admissions.NHSNumber = Provider_APC_2004_05.NHSNumber) or
(Combined_Admissions.PASNUMBER = Provider_APC_2004_05.PDDISTNO)) and
(Combined_Admissions.AdmDate BETWEEN DateAdd(yyyy,-1,Provider_APC_2004_05.AdmDate) AND Provider_APC_2004_05.AdmDate) AND
Combined_Admissions.AdmMethod like 'Emergency%')-- and
-- CA.NHSorPrivate = 'NHS'))
FROM Provider_APC_2004_05, Combined_Admissions
any help in improving speed would be most welcome as there are 3 more of these updates to run right after this one and the analysis tables are almost double the size of this one
Dave
View 6 Replies
View Related
Sep 25, 2006
Hi there
Running query analyzer against two different server.
the first only need 1-2 secs to return the query result,
the other return 7-8 secs for the query result.
plz advice what could cause this slow performance?
thx
View 1 Replies
View Related
Aug 13, 2007
Dear All,
I have created a table in my SQL server database, the problem i am facing is my insert query fails if i leave any form field empty (leave it blank). On my back-end table, only one field is mandatory, and others have been set with the constraint "allow null".
As per our business requirement, except one value is complusory while others are optional. If I enter all values in the form it works perfectly fine. Can you see in the code below - where am i possibly going wrong ?
<script language="VB" runat="server" > Sub Page_Load(Src As Object, e As EventArgs) If Page.IsPostBack Then Dim ConLath As SqlConnection Dim comLath As SqlCommand Dim insertcmd conLath = New SqlConnection("Data Source=SQLas;Initial Catalog=settle;User ID=sa;Password=password") ConLath.Open() insertcmd = "Insert into His_set values (@t_d,@s_p,@p_s,@v_oq,@i_oq,@v_qn,@i_qn,@v_qw,@i_qw)" comLath = New SqlCommand(insertcmd, ConLath) comLath.Parameters.Add(New SqlParameter("@t_d", SqlDbType.DateTime, 12)) comLath.Parameters("@t_d").Value = trade_date.Text comLath.Parameters.Add(New SqlParameter("@s_p", SqlDbType.Decimal, 8)) comLath.Parameters("@s_p").Value = sett_price.Text comLath.Parameters.Add(New SqlParameter("@p_s", SqlDbType.Decimal, 8)) comLath.Parameters("@p_s").Value = post_close.Text comLath.Parameters.Add(New SqlParameter("@v_oq", SqlDbType.Int, 8)) comLath.Parameters("@v_oq").Value = vol_oq.Text comLath.Parameters.Add(New SqlParameter("@i_oq", SqlDbType.Int, 8)) comLath.Parameters("@i_oq").Value = oi_oq.Text comLath.Parameters.Add(New SqlParameter("@v_qn", SqlDbType.Int, 8)) comLath.Parameters("@v_qn").Value = vol_qn.Text comLath.Parameters.Add(New SqlParameter("@v_qw", SqlDbType.Int, 8)) comLath.Parameters("@v_qw").Value = vol_qw.Text comLath.Parameters.Add(New SqlParameter("@i_qn", SqlDbType.Int, 8)) comLath.Parameters("@i_qn").Value = oi_qn.Text comLath.Parameters.Add(New SqlParameter("@i_qw", SqlDbType.Int, 8)) comLath.Parameters("@i_qw").Value = oi_qw.Text
Try comLath.ExecuteNonQuery() Catch ex As SqlException If ex.Number = 2627 Then Message.InnerHtml = "ERROR: A record already exists with " _ & "the same primary key" Else Message.InnerHtml = "ERROR: Could not add record, please " _ & "ensure the fields are correctly filled out" Message.Style("color") = "red" End If End Try
comLath.Dispose() ConLath.Close() End If End Sub
</script>
View 6 Replies
View Related
Sep 26, 2006
Hello,
I have a SQL database where I am attempting to perform a complicated query that I cannot seem to figure out. I am using SQL Server.
I have 4 tables (TableA, TableB, TableC, and TableD). TableA and TableB are guaranteed to have a relationship.
TableC and TableD are guaranteed to have a relationship.
The trick is, I need to link between TableA and TableC essentially using a LEFT JOIN. I need to retrieve all of the values from TableA regardless and the information from TableC and TableD if there is a link, if there isn't a link, then the values from TableC and TableD need to be empty strings.
Does anyone know how I can do this? I've been trying for the last 5 hours without any luck. I feel I'm close, but there is something I feel I'm overlooking.
Thank you SO much for your help!
View 5 Replies
View Related
Oct 10, 2014
I need to convert a OUTER APPLY hint in my query to LEFT JOIN.How it can be done?The code which is presently is this: OUTER APPLY Additional Fields. nodes('/AdditionalFields/AdditionalField') AS AF (C)
View 4 Replies
View Related
Mar 8, 2015
We have a view with many left joins. The original creators of this view might have been lazy or sloppy, I don't know. I have rewritten the query to proper inner joins where required and also nested left joins.
So rather then the following exemplary fragment
select <many items>
from A
left join B on B.id_A = A.id
left join C on C.id_B = B.idthis now looks like
select <many items>
from A
left join (B
join C on C.id_B = B.id
) on B.id_A = A.id
Compilation time of the original view was 18s, of the new rewritten view 4s. The performance of execution is also better (not counting the compile of course). The results of the query are identical. There are about 30 left joins in the original view.
I can imagine that the optimizer has difficulty with all these left joins. But 14s is quite a big difference. I haven't looked into detail in the execution plans yet. I noticed that in both cases the Reason for Early Termination of Statement Optimization was Time Out.
View 9 Replies
View Related
Oct 24, 2013
How to remove space left to right and right to left
If I give limit >60 for first 60 character; limit 60< second 60 character
Result would be check if space at 60 character if yes remove and go the 59 character check then space remove and 58 character check if there is charater then display
As well as after 60 character to till 120 for right space
View 5 Replies
View Related
Mar 8, 2007
This sounds like a pretty easy one. I have a SQL 2000 database with 2-3.4GHZ CPUs and 1GB of RAM. I have one database on it. I go in Query Analyzer on another machine and run a simple query like 'SELECT * FROM USERS' which should return 15,000 rows.
IT takes 30 (thirty) seconds to finish this query. OMG
Where do I start to decipher why on Earth this takes more than .01 seconds?
Thanks.
View 8 Replies
View Related
Mar 16, 2001
Hi,
I have a query which has suddenly started responding slow.
CAn anyone tell me what could be the possibilities?
I tried update stats(I am on sql 70-though it's done auto but i did it manually again)
I used union all in place of union but had no big effect.any othe thought?
Thanks!
View 4 Replies
View Related
Oct 12, 2000
Hi,
I have a query that takes minutes to execute, even through there are about 300,000 records are being processed. I would appreciate any help with optimizing that query.
I have two tables: User and Usage. Table user has two fields: User_Id and Date_Created and a non-clustered index on User_Id. Table usage has two fields also: User_Id and Date_Used and non-clustered index on both fields. The User table is populated when the user registers. The Usage table is populated every time the user opens a document.
Here is what I need to do: get the number of users from the Usage table who opened a document at least once after they have registered during the last 30 days for each day in the time frame, where the time frame varies.
For example, if the time frame is 8/01/00 - 8/31/00, I need to get the following data:
date returns
---- -------
8/01/00 10 (10 users returned to the document between 7/2/00 and 8/1/00)
8/02/00 15 (15 users returned between 7/3/00 and 8/02/00)
.
.
.
8/31/00 20 (20 users returned between 8/1/00 and 8/31/00)
Here is my query:
SELECT [date],
(SELECT count(distinct user_id)
FROM usage u JOIN [user] ON u.[user_id] = [user].[user_id]
WHERE u.[date] BETWEEN usage.[date]-30 AND usage.[date]
AND u.[date]>[user].date_created
GROUP BY usage.[date])returns
FROM usage
WHERE [date] BETWEEN @date1 AND @date2
This query works fine, but too slow. We use MS SQL server 7.0.
Thank you,
Yana
View 2 Replies
View Related
Dec 2, 2002
I have a query which responds immediately when run however if I add an order by clause it takes 40 seconds. Below is the query with the order by clause
SELECT distinct Licenseplate, platetypecode.platetypecode, platetypecode.platetypecodeid
FROM Ticket INNER JOIN PlateTypeCode
ON PlateTypeCode.PlateTypeCodeID = Ticket.PlateTypeCodeID
ORDER BY licenseplate
The Ticket table contains approx. 11,000 records. I have created a nonclustered index for the licenseplate field, a 7 char varchar field.
Any suggestions for speeding up the query?
View 6 Replies
View Related
Jun 15, 2004
The following query is causing some problems because it's taking too long to complete. I looked at the estimated execturion plan and I am unsure why it appears to spend over 50% of its time doing a 'Bookmark Lookup' on on particular column (SRA_SR_ID in the S_EVT_ACT table). There is an index on the column - S_EVT_F14. I'm not sure if the query is using the index properly. What can be done to specifically improve this particular problem? In general, does anyone have some suggestions for optimizing the query as a whole?
Thanks in advance. Clive
SELECT
T1.APPT_REPT_FLG,
T18.X_ALIS_ID,
CONVERT (VARCHAR (10),T1.APPT_START_TM, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_START_TM, 8),
T1.ASGN_USR_EXCLD_FLG,
T2.NAME,
T19.STAT_CD,
T1.APPT_REPT_TYPE,
T15.NAME,
CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 8),
T1.TODO_CD,
T1.X_DOC_CAT_ID,
CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 8),
T1.TARGET_OU_ID,
T7.ZIPCODE,
T3.ZIPCODE,
T9.EXP_RPT_NUM,
T1.LAST_UPD_BY,
T1.OWNER_PER_ID,
T1.PART_RPR_ID,
T1.RATE_LST_ID,
CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 8),
T1.ACTIVITY_UID,
T4.NAME,
T1.PR_TMSHT_LINE_ID,
T18.LAST_NAME,
T7.ADDR,
T18.SEX_MF,
T1.BILLABLE_FLG,
CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 8),
T1.SRA_SR_ID,
T1.TARGET_PER_ADDR_ID,
T18.X_FST_NAME,
T1.EVT_STAT_CD,
CONVERT (VARCHAR (10),T1.X_SCAN_DATE, 101) + ' ' + CONVERT (VARCHAR (10),T1.X_SCAN_DATE, 8),
T1.ROW_STATUS,
T1.ACD_CALL_DURATION,
T5.NAME,
T8.FAX_PH_NUM,
T8.X_FST_NAME,
T8.LAST_NAME,
T1.MODIFICATION_NUM,
T1.X_CAMP_ID,
CONVERT (VARCHAR (10),T1.X_SCAN_TIME, 101) + ' ' + CONVERT (VARCHAR (10),T1.X_SCAN_TIME, 8),
T1.ASSOCIATED_COST,
T13.NAME,
CONVERT (VARCHAR (10),T1.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T1.LAST_UPD, 8),
T17.TMSHT_NUM,
T1.PR_SYMPTOM_CD,
T1.OPTY_ID,
CONVERT (VARCHAR (10),T18.BIRTH_DT, 101) + ' ' + CONVERT (VARCHAR (10),T18.BIRTH_DT, 8),
T1.PR_EXP_RPT_ID,
CONVERT (VARCHAR (10),T1.APPT_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_START_DT, 8),
T8.FST_NAME,
T16.SR_NUM,
T1.SRA_DEFECT_ID,
T1.CREATED_BY,
T8.WORK_PH_NUM,
CONVERT (VARCHAR (10),T1.COST_EXCH_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.COST_EXCH_DT, 8),
T1.CALL_ID,
T1.X_CLIENT_ID,
T1.PROJ_ID,
T12.DEFECT_NUM,
T1.CREATOR_LOGIN,
T1.CONFLICT_ID,
T19.OUTCOME_CD,
T1.TEMPLATE_FLG,
T2.PR_ADDR_ID,
T1.PREV_ACT_ID,
T1.X_DOC_NAME,
T1.EXP_RLTD_FLG,
T1.X_BATCH_REF,
T1.PRI_LST_ID,
T1.SRC_ID,
T1.X_POLICY_REF,
CONVERT (VARCHAR (10),T1.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T1.CREATED, 8),
T1.EMAIL_FORWARD_FLG,
T11.DMT_NUM,
T1.TMSHT_RLTD_FLG,
T1.ROW_ID,
T10.NAME,
T18.CONSUMER_FLG,
T1.TARGET_PER_ID,
T18.FST_NAME,
T1.PRIV_FLG,
T3.PROVINCE,
T8.X_ALIS_ID,
T8.JOB_TITLE,
T14.NAME,
T1.NAME,
T1.PCT_COMPLETE,
T1.SRA_TYPE_CD,
T1.ALARM_FLAG,
T1.CAL_DISP_FLG,
T1.EVT_PRIORITY_CD,
T1.COST_CURCY_CD,
T2.LOC,
CONVERT (VARCHAR (10),T1.TODO_ACTL_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_START_DT, 8),
T20.FILE_NAME,
T1.SRA_RESOLUTION_CD,
T6.PRDINT_ID,
T1.OWNER_LOGIN
FROM
dbo.S_EVT_ACT T1
LEFT OUTER JOIN dbo.S_ORG_EXT T2 ON T1.TARGET_OU_ID = T2.ROW_ID
LEFT OUTER JOIN dbo.S_ADDR_ORG T3 ON T2.PR_ADDR_ID = T3.ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T4 ON T1.PRI_LST_ID = T4.ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T5 ON T1.RATE_LST_ID = T5.ROW_ID
LEFT OUTER JOIN dbo.S_ACT_PRDINT T6 ON T1.ROW_ID = T6.ACTIVITY_ID
LEFT OUTER JOIN dbo.S_ADDR_PER T7 ON T1.TARGET_PER_ADDR_ID = T7.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T8 ON T1.TARGET_PER_ID = T8.ROW_ID
LEFT OUTER JOIN dbo.S_EXP_RPT T9 ON T1.PR_EXP_RPT_ID = T9.ROW_ID
LEFT OUTER JOIN dbo.S_OPTY T10 ON T1.OPTY_ID = T10.ROW_ID
LEFT OUTER JOIN dbo.S_PART_RPR T11 ON T1.PART_RPR_ID = T11.ROW_ID
LEFT OUTER JOIN dbo.S_PROD_DEFECT T12 ON T1.SRA_DEFECT_ID = T12.ROW_ID
LEFT OUTER JOIN dbo.S_PROD_INT T13 ON T6.PRDINT_ID = T13.ROW_ID
LEFT OUTER JOIN dbo.S_PROJ T14 ON T1.PROJ_ID = T14.ROW_ID
LEFT OUTER JOIN dbo.S_SRC T15 ON T1.SRC_ID = T15.ROW_ID
LEFT OUTER JOIN dbo.S_SRV_REQ T16 ON T1.SRA_SR_ID = T16.ROW_ID
LEFT OUTER JOIN dbo.S_TMSHT_LINE T17 ON T1.PR_TMSHT_LINE_ID = T17.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T18 ON T1.X_CLIENT_ID = T18.ROW_ID
LEFT OUTER JOIN dbo.S_CAMP_CON T19 ON T1.X_CAMP_ID = T19.SRC_ID AND T1.TARGET_PER_ID = T19.CON_PER_ID
LEFT OUTER JOIN dbo.S_ACTIVITY_ATT T20 ON T1.ROW_ID = T20.PAR_ROW_ID
WHERE
((T1.APPT_REPT_FLG != 'Y' OR T1.APPT_REPT_FLG IS NULL) AND
(T1.TEMPLATE_FLG != 'Y' AND T1.TEMPLATE_FLG != 'P' OR T1.TEMPLATE_FLG IS NULL)) AND
(T1.SRA_SR_ID = '1-EQLOO')
View 3 Replies
View Related
Oct 12, 2006
I have a query that is taking too long to run. It take 14 seconds to return 6800 rows. However, if I move the query out of a stored proc, it takes 1 second. I want to understand this issue and ideally fix the stored proc case.
I've simplified my actual queries for readability.
-- @filter is value to filter against or NULL to return all records.
CREATE PROCEDURE queryPlayerStations(@filter INTEGER)
AS
SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@filter IS NULL) OR (MyTable.Column = @filter))
GO
DECLARE @filter INTEGER
SET @filter = NULL
-- Takes 14 seconds to return 6800 rows. That's unacceptable performance
EXEC dbo.queryPlayerStations @filter
When I run the query directly in Query Analyzer, it runs very fast.
DECLARE @filter INTEGER
SET @filter = NULL
-- Takes ~1 second to return 6800 rows. That's great performance
SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@filter IS NULL) OR (MyTable.Column = @filter))
When I put the parameters in the stored proc it runs fast.
CREATE PROCEDURE queryPlayerStations
AS
DECLARE @filter INTEGER
SET @filter = NULL
SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@filter IS NULL) OR (MyTable.Column = @filter))
GO
-- Takes ~1 second to return 6800 rows. That's great performance
EXEC dbo.queryPlayerStations
Anyone have any ideas what I can do to improve the stored proc case?
View 2 Replies
View Related
Mar 27, 2008
Hi guys/gals
I have only just signed up, as I have a problem thats confusing me a lot
I have a page (classic asp - sadly) that displays a list of invoices for a certain customer
Each of these customers has fairly complex permissions system, but the page generates a list of invoices and values in 0.01 seconds
However in the while...do loop, for each invoice it checks a database table for "extra charges"..
This is a simple SQL query
"Select Sum(amount) from extra_charges where invoice_number=" & current_invoice_number
However each time it runs this simple select statement it takes 2 seconds - which causes the page to load in 70 seconds instead of 0.01
I am absolutely beyond confused. I have tried it in a stored procedure, and a direct ado query..
The table by the way is empty on my current test system, and one one other system has about 150 records in it. Both take the same time
Any help would be appreciated?
This is a MSSQL 2000 database I've inherited, I am usually a PHP/MYSQL developer - so im a little out of my field of expertise here
View 13 Replies
View Related
May 26, 2004
i want to have a like search in the following query.
SELECT DISTINCT TOP 200 a.AccountID,
a.AccountNumber,
c.CLI,
con.SurName,
addr.Address1 [Account Address],
addr.Postcode as [Account Postcode],
atp.Name AS Type,
cs.Code AS Status
FROM account_t a
INNER JOIN customer_t cust on a.customerID = cust.CustomerID
INNER JOIN AccountType_T atp on cust.AccountTypeID = atp.AccountTypeID
INNER JOIN CustomerStatus_T cs ON a.CustomerStatusID = cs.CustomerStatusID
INNER JOIN Contacts_T con on cust.MasterContactID = con.ContactID
INNER JOIN Address_T addr ON cust.MasterAddressID = addr.AddressID
LEFT OUTER JOINCLI_T c ON a.AccountID = c.AccountID
WHERE (c.CLI LIKE @CLI + '%')
AND (con.SurName LIKE @Surname + '%')
AND (addr.Address1 LIKE @Address + '%')
AND (REPLACE(addr.Postcode, ' ', '') LIKE @Postcode + '%')
AND c.DateArchived IS NULL
here all fields @CLI, @Surname, @Address, @Postcode are varhcar types...which is making this query very slow...is there any suggestion to improve this query?
thanks in advance
bhavya
View 11 Replies
View Related
Mar 18, 2008
When I want to display the total records (#) in a webpage, it is very slow. When I try to remove the total records and show them per 20s, it responds very fast.
Any ideas?
View 2 Replies
View Related
Sep 13, 2006
What might be going on here? The Query is against a single table withsome criteria. The database is active with upto 200 connected usersand at peak times there are 10 or more active sessions. Most of thetime, the query comes back in milliseconds. Occasionally though, it itcan take a whole minute. I've been watching CPU, Memory, Disk. Noneof these appear to be the bottlenecking. (CPU usually below 10% andalways below 50%, pages/sec is 0, and disk % is low and does notspikes during hangs) I also checked to see if hangs were synching withTlog backups or other scheduled jobs, but that is not the case. Thebox has good hardware 4GB RAM and 2 CPU at 3.4 GHz. What could beholding this query up?Thanks for any ideas.Dave
View 1 Replies
View Related
Jul 20, 2005
This UNION query is very slow. With only 3,000 records in the Parent tableand 7,000 records in the Child table, it takes about 60 seconds to run andreturns about 2200 records.Any ideas on speeding it up? Thanks.-- PART 1: HAS NO CHILD RECORDSSELECT P.PROJECT_ID, 'No Child Data' AS SUB_TYPEFROM PROJECTS P LEFT JOIN PROJECTS_CHILDREN CON P.PROJECT_ID = C.PROJECT_IDWHERE P.PROJECT_ID IS NULLUNION-- PART 2: HAS CHILD RECORDS, BUT NOT OF TYPE ZSELECT PROJECT_ID, 'Child Data, Not type Z' AS SUB_TYPEFROM PROJECTSWHERE PROJECT_ID NOT IN((SELECT PROJECT_IDFROM PROJECTS_CHILDRENWHERE CHILD_TYPE Like "Z*")ANDPROJECT_ID NOT IN (SELECT P.PROJECT_IDFROM PROJECTS P LEFT JOIN PROJECTS_CHILDREN CON P.PROJECT_ID = C.PROJECT_IDWHERE P.PROJECT_ID IS NULL));
View 4 Replies
View Related
