We have a query (a few actually) which runs for about 30 secs via Siebel 6 and the same query takes on 1 or 2 secs in Query Analyser, consistently. Naturally this performance problem is causing issues. We're wondering if the App isn't using the same execution plan, or using the indexes or....
It might be worth noting that the query returns 1 or no rows.
(I can add the query and more detail if anyone really, really wants :rolleyes: )
The devlopers have created a VB app which mimmics to app running the query and we've put it through proflier, results..
Duration Reads CPU
Siebel Query 28300 3661280 23984
It seems a high number of reads there, and the result from QA is SO much faster.
Any ideas welcome, thanks.
I have a table which has a few fields, one being "datetime_traded". I need to write a query which returns the row which has the closest time (down to second) given a date/time. I'm using MS SQL.
Here's what I have so far:
Code:
select * from TICK_D where datetime_traded = (select min( abs(datediff(second,datetime_traded , Convert(datetime,'2005-05-30:09:31:09')) ) ) from TICK_D)
But I get an error - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".
Does anyone know how i could do this? Thanks a lot for any help!
Select 'PIT_ID' = CASE WHEN Best_BID_DATA.PIT_ID IS NOT NULL THEN Best_BID_DATA.PIT_ID ELSE Best_OFFER_DATA.PIT_ID END, Best_Bid_Data.Bid_Customer, Best_Bid_Data.Bid_Size, Best_Bid_Data.Bid_Price, Best_Bid_Data.Bid_Order_Id, Best_Bid_Data.Bid_Order_Version, Best_Bid_Data.Bid_ProductId, Best_Bid_Data.Bid_TraderId, Best_Bid_Data.Bid_BrokerId, Best_Bid_Data.Bid_Reference, Best_Bid_Data.Bid_Indicative, Best_Bid_Data.Bid_Park, Best_Offer_Data.Offer_Customer, Best_Offer_Data.Offer_Size, Best_Offer_Data.Offer_Price, Best_Offer_Data.Offer_Order_Id, Best_Offer_Data.Offer_Order_Version, Best_Offer_Data.Offer_ProductId, Best_Offer_Data.Offer_TraderId, Best_Offer_Data.Offer_BrokerId, Best_Offer_Data.Offer_Reference, Best_Offer_Data.Offer_Indicative, Best_Offer_Data.Offer_Park
from ( Select PITID PIT_ID, CustomerId Bid_Customer, Size Bid_Size, Price Bid_Price, orderid Bid_Order_Id, Version Bid_Order_Version, ProductId Bid_ProductId, TraderId Bid_TraderId, BrokerId Bid_BrokerId, Reference Bid_Reference, Indicative Bid_Indicative, Park Bid_Park From OrderTable C Where version = (select max(version) from OrderTable where orderid = c.orderid) and BuySell = 'B' and Status <> 'D' and Park <> 1 and PitId in (select distinct pitid from MarketViewDef Where MktViewId = 4) and Price = ( Select max(Price) From OrderTable cc where version = (select max(version) from OrderTable where orderid = cc.orderid) and PitId = c.PitId and BuySell = 'B' and Status <> 'D' and Park <> 1 ) and Orderdate = ( Select min(Orderdate) From OrderTable dd where version = (select max(version) from OrderTable where orderid = dd.orderid) and PitId = c.PitId and BuySell = 'B' and Status <> 'D' and Price = c.Price and Park <> 1 ) and OrderId = (select top 1 OrderId from OrderTable ff Where version = (select max(version) from OrderTable where orderid = ff.orderid) and orderid = ff.orderid and PitId = c.PitId and BuySell = 'B' and Status <> 'D' and Price = c.Price and Orderdate = c.Orderdate and Park <> 1 )
) Best_Bid_Data
full outer join ( Select PITID PIT_ID, CustomerId Offer_Customer, Size Offer_Size, Price Offer_Price, orderid Offer_Order_Id, Version Offer_Order_Version, ProductId Offer_ProductId, TraderId Offer_TraderId, BrokerId Offer_BrokerId, Reference Offer_Reference, Indicative Offer_Indicative, Park Offer_Park From OrderTable C Where version = (select max(version) from OrderTable where orderid = c.orderid) and BuySell = 'S' and Status <> 'D' and Park <> 1 and PitId in (select distinct pitid from MarketViewDef Where MktViewId = 4) and Price = ( Select min(Price) From OrderTable cc where version = (select max(version) from OrderTable where orderid = cc.orderid) and PitId = c.PitId and BuySell = 'S' and Status <> 'D' and Park <> 1 ) and Orderdate = ( Select min(Orderdate) From OrderTable dd where version = (select max(version) from OrderTable where orderid = dd.orderid) and PitId = c.PitId and BuySell = 'S' and Status <> 'D' and Price = c.Price and Park <> 1 ) and OrderId = (select top 1 OrderId from OrderTable ff Where version = (select max(version) from OrderTable where orderid = ff.orderid) and orderid = ff.orderid and PitId = c.PitId and BuySell = 'S' and Status <> 'D' and Price = c.Price and Orderdate = c.Orderdate and Park <> 1 )
) Best_Offer_Data ON Best_Bid_Data.Pit_Id = Best_Offer_Data.Pit_Id
My asp.net application is attached with SQL database, I record only time in my SQL database , field type is nvarchar. Now I want to do qurey by time and pull the result , but datatype of field in nvarchar, query does not giving me right result. some one tell me how Do I do query so I can get proper result. thanks maxmax
I have asp.net applicatin with SQL database communicating. in database I have date field and time field. Now I wan to do SQL query which can pull informatin on particular date between given start time to given end time Can some one show me sample SQL query so I can pull informatin on particular day between two times
How do I find all the records within a time range when all the datetime's are on different days? Example: Show me all the order placed between 12:00 and 15:00 in May
I need to time a query down to hundredths of a second. I can see from the runtime clock in SQL Studio that my query ran for 00:00:00. However, I need to know exactly how long it took.
Hello,I have a table that lists a number of available time slots for a party venue. I want a user to select a particular time slot and have the query show results for that time slot plus two time slots before and two time slots after the user's selection. If the user selects a time slot that is the first of the day, I still want that user to be shown 5 results including the one he selected. The same goes for the last time slot of the day. Can anyone help me with the SQL statement? Thank you very much in advance! Mark
I am using SQL Server and ASP.NET. I am executing a couple of stored procedures and displaying the results in a datagrid. Since these Stored procedures takes around 2-4 minutes each, I want to display a status bar on the web by displaying the approximate time the user needs to wait before seeing the results.
My question is: Is there a way to find out the approximate EXECUTION TIME of the stored procedure before hand. Also, if that is possible, how do i access the same from the ASP.NET code..
I have a table which has a field called time and it has data like '23/04/2004 9:43:40 AM' How would i write a query that would retrieve the last hour of data from now().
i've tried this but does not work,
SELECT [time] FROM table WHERE ([time] = { fn NOW() } - 60)
I have a server with two test instances of a data base. I have a query which creates a temp table, inserts 29 rows, perform 4 update queries to add counts and then dumpps out the results. This entire query script runs 1.33 minutes on one instance and 2.5 minutes on the other. On the production server this query now runs in 9 seconds. If I run any one of the test updates individually they execute under 2 seconds, just like the production server. THe results are repeatable.
All are SQL 7 with all service packs on NT4 sp6. Both test data bases are backups of production from last week. I suspect some kind of caching/buffer problem, but I do not know what to look for. I am not a DBA so I have no idea what role TEMPDB plays may play in this.
Can anyone give us ideas on where to look for the performance difference? Will our impending upgrade to SQL2K solve this problem or make it worse? Any ideas would be appreciated.
Hi, We have a SQL 7 / Win2K cluster and yesterday afternoon the users were complaining about poor performance. Their queries were timing out.. (Not all of them, just some on some large tables)
I ran just an ad-hoc query against the table from my machine and I also timed out. THen I went right to the box that had control of the cluster and did the same thing there and also timed out. Because of time constraints (and we are in testing mode) we tested a failover and everything was back to normal after that.
So now we want to try to figure out what could have been the problem. At the time I checked out the Memory and CPU usage and they were very low (0-5%) and using only 1/3 of the memory. It couldn't be a bad query or index because after the failover it worked fine.
Could there be something wrong with the specific box that had control at the time? I dont' know where to look?
I'm having a real problem with this query at the moment... Basically I have to produce a query which will tell me the total number of people employed by the company at any given date and the total salary for all these people.
We have a people table and a career table. People(unique_identifier, known_as_and_surname, start_date, termination_date ...) Career(unique_identifier, parent_identifier, career_date, basic_pay ...) Relationship people.unique_identifier = career.parent_identifier
Employees can be identified like so
SELECT * FROM people WHERE start_date <= DateSelected AND (termination_date > DateSelected OR termination_date IS NULL)
Passing the selected date to the query is no trouble at all I am just having problems with the point in time side of this.
All and any help is greatly appreciated :) ~George
those query would get me a return of value inside of datetime coloumn, which is, date of (assume i run the query today) 2014-05-12 07:00:00 until 2014-05-30 16:00:00, my question is where is the date of 2014-05-01 07:00:00 until 2014-05-11 16:00:00 goes ??
DECLARE @StartDate DateTime DECLARE @EndDate DateTime SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0) SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0)) select Datetime from InterfaceTraffic where (DateTime between @StartDate and @EndDate)
[code]...
it would get me a return of all dates of last month with all hours and what i want is to have a return of all dates of last month but with specific hour only (the hour between 7 am until 5 pm)
I have a Stored Procedure that execute some queries on link server. It takes so long to complete so my application get timeout error. There was no problem until last week. I suspect, remote queries that qorks on link server takes long. How can i trace the time of queries. Any idea about link server timeout problems?
Dear All, here i'm posting my query which is taking 3 minutes
please suggest me the best query
SELECT distinct INP.COLUMN001 REPORT_INPUT_ID, INP.COLUMN002 REPORT_ID, INP.COLUMN003 OPERATION_ID, OPER.COLUMN004 OPERATION_CODE, OPER.COLUMN005 OPERATION_NAME, INP.COLUMN004 ITEM_ID, CONVERT(NVARCHAR , INP.COLUMN005, 110) RECEIVED_DATE, INP.COLUMN006 LOT_NO, INP.COLUMN007 RECEIVED_QTY, INP.COLUMN008 CONSUMED_QTY, (select CODE from view1 where item_id = INP.COLUMN004) my_val, (select NAME from view1 where item_id = INP.COLUMN004) Item_Name, INP.COLUMN009 UOM_ID, U.UOM_CODE, INP.COLUMN010 BASE_RECEIVED_QTY, INP.COLUMN011 BASE_CONSUMED_QTY, case when INP.COLUMN012 ='1' then 'Progress' when INP.COLUMN012 ='2' then 'Closed' end OPERATION_STATUS, case when INGDTL.COLUMN006 ='0' then 'Ingredient' when INGDTL.COLUMN006 ='1' then 'Intermediate' end INPUT_TYPE, INP.COLUMNB01 COLUMNB01, INP.COLUMNB02 COLUMNB02, INP.COLUMNB03 COLUMNB03, INP.COLUMNB04 COLUMNB04, INP.COLUMNB05 COLUMNB05, INP.COLUMNB06 COLUMNB06, INP.COLUMNB07 COLUMNB07, INP.COLUMNB08 COLUMNB08, INP.COLUMNB09 COLUMNB09, INP.COLUMNB10 COLUMNB10, INP.COLUMND01 BRANCHID, INP.COLUMND02 COMPANYID, INP.COLUMND03 CREATEDBY, INP.COLUMND04 CREATEDDATE, INP.COLUMND05 LASTUPDATEDBY, INP.COLUMND06 LASTUPDATEDDATE, INP.COLUMND07 ROWGUID, INP.COLUMND08 UPDATEDSITE, INP.COLUMND09 LANGID, WC.COLUMN009 WIP_WAREHOUSE_ID, (SELECT (sum(WIP.COLUMN011) - sum(wip.column010)) FROM TABLE066 WIP where wip.column008 = INP.column004 and WIP.COLUMN005 = '8cd741c7-1ac6-4839-88e7-df85518170f1' and wip.column006 = inp.column003 ) WIP_Qty , WIPM.Column005 WIP_ITEM_ID FROM TABLE073 INP left join view1 I on I.ITEM_ID = INP.COLUMN004 left join view2 U on U.UOM_ID = INP.COLUMN009 left join TABLE022 OPER ON OPER.COLUMN001 = INP.COLUMN003 left join TABLE066 WIP on WIP.column008 = INP.column004 left join TABLE015 WC on WC.COLUMN001 = OPER.COLUMN008 left JOIN TABLE040 INGDTL ON INGDTL.COLUMN002 = INP.COLUMN004 AND WIP.column008 = INGDTL.COLUMN002 left join TABLE065 WIPM on WIPM.column005 = INP.column004 where INP.COLUMN002 = '057f87aa-7884-43fa-8984-9b74c971da62' order by my_val
Hi, when I execute the sql in DBTestArchive and then DBTest query analyzer , I found that the run time is shorter in DBTest. Can I make assumption that the query run time is shorter in DBTest if I select data from it and insert into another database? Thanks.
INSERT INTO DBTestArchive.dbo.tblVendorMasterArchive SELECT * FROM DBTest.dbo.tblVendorMaster
Consider this SQL Query:-----------------------------------------------------------------SELECT c.CASE_NBR, DATEDIFF(d, c.CREATE_DT, GETDATE()) AS Age,c.AFFD_RCVD, c.PRV_CRD_ISS, x.RegE, x.Type, x.Fraud,c.CUST_FN + ' ' + c.CUST_LN AS CustFullName,c.ATM_CKCD_NBR, x.TotalLoss, x.Queue, x.Status,c.QUEUE AS Expr1, x.CHECK_ACT_NBR, c.CUST_LN, c.SSN,c.CREATE_DTFROM (SELECT TOP 9999999 cl.CASE_NBR, cl.SSN, cl.CREATE_DT,SUM(cast(TRANS_AMNT AS float)) AS TotalLoss,glQueue.REFN_NM AS Queue,glStatus.REFN_NM AS Status,grRegE.REFN_NM AS RegE, grType.REFN_NM AS Type,grFraud.REFN_NM AS Fraud, cl.CHECK_ACT_NBRFROM (((((T_CASE_LST AS cl LEFT JOINT_INCIDENT_LST AS il ON cl.CASE_NBR = il.CASE_NBR)INNER JOIN T_GNRL_REFN AS glQueueON cl.QUEUE = glQueue.REFN_NBR)INNER JOIN T_GNRL_REFN AS glStatusON cl.STATUS_CD = glStatus.REFN_NBR)INNER JOIN T_GNRL_REFN AS grRegEON cl.REGE_CD = grRegE.REFN_NBR)INNER JOIN T_GNRL_REFN AS grTypeON cl.CASE_TYPE_CD = grType.REFN_NBR)INNER JOIN T_GNRL_REFN AS grFraud ON cl.FRAUD_CD =grFraud.REFN_NBRWHERE (((glQueue.REFN_DESC) = 'Queue')AND ((glStatus.REFN_DESC) = 'STATUS_CD')AND ((grRegE.REFN_DESC) = 'YesNo')AND ((grType.REFN_DESC) = 'Fraud_Code')AND ((cl.STATUS_CD) = 0))GROUP BY cl.CASE_NBR, glQueue.REFN_NM, glStatus.REFN_NM,grRegE.REFN_NM, grType.REFN_NM, grFraud.REFN_NM,grFraud.REFN_DESC, cl.CHECK_ACT_NBR,cl.SSN, cl.CREATE_DTHAVING (((grFraud.REFN_DESC) = 'YesNo'))) xLEFT OUTER JOIN T_CASE_LST c ON x.CASE_NBR = c.CASE_NBR-----------------------------------------------------------------1. Is there anything that can be done to speed up the query?2. This part of the query: ... AND ((cl.STATUS_CD) = 0 ... where the 0is actually a variable passed in via a VB application. 0 would be newcases, and normally return around 4000 - 5000 records.3. The SQL server, Web Server, and users, are all in different states.4. The time to return this query where cl.STATUS_CD = 0 is about 7 -12 seconds.5. Is this a reasonable time for this query? What can be done toincrease the time?6. The SQL server is indexed on T_CASE_LST.STATUS_CD andT_INCIDENT_LST.CASE_NBR, but not on any field from T_GNRL_REFN sinceT_GNRL_REFN is only a general lookup table, and contains less than 50records.7. I've built the query as a stored procedure, and it works, though nomeasurable speed increase was obtained.8. I have not attempted building a view to aid this, as I don't seethat helping... or will it?9. Well: any ideas?10. I would gladly rewrite the SQL Query if it could return the samedata faster using another method.11. Is there a way to accomplish the joins involved with theT_GNRL_REFN in another manner to make it quicker?12. Is there a better way to add the values in T_INCIDENT_LST than:.... SUM(cast(TRANS_AMNT AS float)) AS TotalLoss ... ?13. I don't care if its pretty, I just need it faster.14. How can I get the summing of T_INCIDENT_LST.TRANS_AMNT without aderived table...? I know that using the derived table is slowing itdown some.**** Any Ideas ****David
Hi All.I have some rather large SQL Server 2000 databases (around 60GB).I have set up jobs to re-index the tables and update statistics everysunday. This worked will for a few months. Now after a day or two ofusing it the connections to it keep timing out. If i start the jobsmanually, all is well for two days or so.Surely there can be a better solution to this ?TIA.Ryan,.
