I have a query in access that is running extremely slow and I'm trying to find a better way to write it. It appears to be the criteria statement that is causing the lag. Is there a better way to write this? And unfortunately I have to keep it in access.
query:
SELECT DISTINCT "JBC" AS ClientCode, PaymentDetail.PatientNumber, Procedures.CaseNumber, IIf(IsNull([TicketNumber]),Procedures.patientnumber & Year(Procedures.dateofservice) & Month(Procedures.dateofservice) & Day(Procedures.dateofservice),Procedures.ticketnumber) AS ClaimNumber, PaymentDetail.PaymentCounter, PaymentDetail.TransAmount, Payments.PaymentDate, PaymentDetail.AccountingDate, PaymentDetail.TransDate, payments.PaymentType & "-" & Adjustments.adjustmentcode AS CombinedPmtType, Payments.PaymentType, Payments.PaymentCode, Adjustments.AdjustmentCode, PaymentCodes.BriefDescription, PaymentCodes.LongDescription, Payments.CarrierCode, Payments.Remarks, Procedures.ProcedureCode, Procedures.DateOfService, PaymentDetail.DetailCounter
FROM ((((PaymentDetail LEFT JOIN Procedures ON (PaymentDetail.PaymentCounter = Procedures.Counter) AND (PaymentDetail.AccountingDate = Procedures.AccountingDate) AND (PaymentDetail.PatientNumber = Procedures.PatientNumber)) LEFT JOIN Payments ON (PaymentDetail.TransDate = Payments.AccountingDate) AND (PaymentDetail.TransCounter = Payments.Counter) AND (PaymentDetail.PatientNumber = Payments.PatientNumber)) LEFT JOIN PaymentCodes ON Payments.PaymentCode = PaymentCodes.PaymentCode) LEFT JOIN Adjustments ON (PaymentDetail.TransDate = Adjustments.AccountingDate) AND (PaymentDetail.PatientNumber = Adjustments.PatientNumber)) LEFT JOIN AdjustmentCodes ON Adjustments.AdjustmentCode = AdjustmentCodes.AdjustmentCode
WHERE (((Procedures.CaseNumber)=0) AND ((PaymentDetail.TransAmount)<>0) AND ((Payments.PaymentType) Is Null) AND ((PaymentDetail.DetailCounter)=2)) OR (((Procedures.CaseNumber)=0) AND ((PaymentDetail.TransAmount)<>0) AND ((Payments.PaymentType) Is Null) AND ((PaymentDetail.DetailCounter)=5) AND ((AdjustmentCodes.InsuranceIndicator)="N"));
I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
I have a text box that is used to submit stock symbols that are to be saved in a sql table. The symbols are to be separated by a space or a comma (I don't know which, yet). I want to retrieve the symbols later to be used in a query, but I don't know how to get the symbols in the proper string format for the query, eg
The symbols are stored in the tables as: A B C D The query string criteria would look like: IN('A', 'B', 'C', 'D')
The IN('A', 'B', 'C', 'D') citeria would be the values in the @Symbol variable in this SPROC
SELECT a_Name_Symbol.Symbol, a_Financials.Revenue FROM a_Financials INNER JOIN a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol WHERE (a_Name_Symbol.Symbol @Symbol) ORDER BY a_Name_Symbol.Symbol
Is there a slick (ie easy) way to change the contents entered in the text box (A B C D) into IN('A', 'B', 'C', 'D') ?
I have a database with some over normalized tables in it. The best I can do with one query is get the file ID. In the second query I want to get all the file names, based on all the fileID's I got from the first query. How would I go about doing this?
I have four criterias in my .aspx page. They are "First Name, Last Name, Title, Year". I have a Book and an Author table. The Author table would contain all the author's information and the Book table contains all the books information such as title, publisher, subject, and so on. So here's what I'm trying to do.
I want to write a transaction statement that will query the four criterias above if the criteria textbox is not black. So for example, when the user click the submit button, all the four criteria fields are filled except Year. That means the query would search the Author and Book tables for "First Name, Last Name, Title" but not "Year" for any potential matches. I also wanted to use "Like" instead of "=" for a wider search.
Actually I'm trying to create a store procedure that will accept those four criterias and search the tables based on those criterias.
SELECT * FROM TableA A JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID WHERE B.SomeParamColumn = @SomeParam
SELECT * FROM TableA A JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID AND B.SomeParamColumn = @SomeParam
Both of these queries return the same result set, but the first query filters the results in the WHERE clause whereas the the second query filters the results in the JOIN criteria. Once upon a time a DBA told me that I should always use the syntax of the first query (WHERE clause). Is there any truth to this, and if so, why?
I'm fairly new in SQL. Been trying for months to create the right script for this particular case but still cannot give me 100% result as required.
SCENARIO :
I am required to query from 2 tables for those unique record that meets both conditions below:- 1. Status is 1 @ max (trans_id), paychnl = CC 2. Status is 2 @ max (trans_id), paychnl = A or B
FYR, 2 tables and respective columns to query are as below:- table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE table CHFF --> col STATUS,PAYCHNL
FYI, status refers to the paychnl method status:- ==> 1 means the current paychnl method ==> 2 means the previous paychnl method
paychnl method can be multiple because it will be defined as 2 for all the histories' paychnl chosen earlier, but 1 should only be unique as it is the latest paychnl chosen for each unique ID. however, it may appear more than once when it's taking those in earlier TRANSDATE, so here we would need the max trans_id as it will show the latest updated TRANSDATE.
Apart from that, I need only those most recent paychnl to be A or B and the latest paychnl is CC so, this been indicated by the same max trans_id for the same ID.
Aft trying so many times on this MAX command but failed to get any result, I only managed to come up to this part only. please refer below:-
table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE table CHFF --> col STATUS,PAYCHNL
FROM DBO.PTFF PTFF JOIN DBO.CHFF CHFF ON CHFF.ID = PTFF.ID WHERE PTFF.TRANSDATE BETWEEN 130501 AND 130831 AND PTFF.TRANSCODE='T522' AND (CHFF.STATUS=1 AND CHFF.PAYCHNL='CC' OR (CHFF.STATUS=2 AND (CHFF.PAYCHNL='A' OR CHFF.PAYCHNL='B')))
However, the script above returns :-
1. All those records with STATUS 1 regardless paychnl is A or B in most recent status 2, 2. Expected results also appear ==> 1 same ID with status 1 while paychnl=CC and status 2 while paychnl=A or B 3. Also duplicates of expected results but for different TRANSDATE and not at MAX TRANS_ID
Getting a weird error while trying out a query from Access 2003 on aSQL Server 2005 table.Want to compute the amount of leave taken by an emp during the year.Since an emp might be off for half a day (forenoon or afternoon), havethe following computed field:SessionOff: ([ForenoonFlag] And [AfternoonFlag])The query works fine when there's no criterion on SessionOff.However, when I try to get the records where the SessionOff equals 0, Iget the following error:~~~~~ODBC--call failed. [Microsoft][SQL Native Client][SQL server]Incorrect syntax near the keyword 'NOT'. (#156)~~~~~I checked the SQL of the Access query, but there's no NOT anywhere init:~~~~~SELECT tblWorkDateAttendance.*FROM tblWorkDate INNER JOIN tblWorkDateAttendance ONtblWorkDate.WorkDate = tblWorkDateAttendance.WorkDateWHERE (((([ForenoonFlag] And [AfternoonFlag]))=0) AND((tblWorkDateAttendance.WorkDate)<Date()) AND((Year([tblWorkDate].[WorkDate]))=Year(Date())) AND((Weekday([tblWorkDate].[WorkDate])) Between 2 And 6) AND((tblWorkDate.HolidayFlag)=False));~~~~~What gives?
I am trying to write a query that gives me the personal records from speed skaters on e.g. the 500 mtrs. I do this with the query:
SELECT cdsDistance AS Distance , prsFirstName , prsLastName , min(crtFinalTime) AS MinTime FROM tb....... INNER JOIN etc.. GROUP BY cdsDistance, prsFirstName, prsLastName ORDER BY min(crtFinalTime)
In itself this works fine. However, there are complicating factors. Sometimes a speed skater has multiple PRs, meaning the he/she has the same fastest time more than once.
If these times are achieved on multple days, the 1st date is the official PR. (meaning: "Min of racedate") If they are raced on the same day the 1st race is the PR (meaning: "Min of distancenumber")
Changing the code to:
SELECT cdsDistance AS Distance , prsFirstName , prsLastName , MIN(crtFinalTime) AS MinTime , MIN(cdsStartDate) AS RaceDate , MIN(cdsDistanceNumber) AS DistanceNumber
FROM tb....... GROUP BY cdsDistance, prsFirstName, prsLastName ORDER BY min(crtFinalTime)
This gives me the wrong outcome because it gives me the "MIN" of every field, and they are not necessarily on the same row.
An option would be to calculate min(crtFinalTime), if for a person there is more than 1 result, calculate min of date, and then (if there is still more than 1 row) min of distancenumber.
Seems complicated, and I have the feeling there must be a better way (apart from: how to get this code)
Stacking subqueries in the FROM statement seems like a option be costly (time wise). There are more than 10 million rows (and growing) to run through.
As an example a few times:
DistanceFirst nameLast name Time Date Distance nr. 500 Yuya Oikawa 34.49 201311155 500 Yuya Oikawa 34.49 201311153 500 Yuya Oikawa 34.49 201311172
Yuya has 3 best times (34.49), 15-11-2013 is the 1st date, then distance nr 3 is the 1st distance raced. Therefore the 2nd row is the only row I would like to get in my endresult.
Criteria Retrieve records with independent price and its total volume per minute
SELECT SUBSTRING(st,1,4) AS Ttime,d_price AS Price,SUM(l_cum) AS Volume FROM cmd4 WHERE sd='20060717' AND serial='0455' GROUP BY SUBSTRING(st,1,4),d_price,l_cum
Can€™t understand the Query execution interval, for every one criteria it takes extra 60 seconds. Help please?,
Query A
SELECT TOP 15 * FROM CMD4 AS T1 WHERE T1.ST IN
(SELECT max(T2.ST) FROM CMD4 AS T2 WHERE T1.SERIAL=T2.SERIAL
AND T2.sd='20060817') AND sd='20060817'
--AND STOCKNAME like 'A%'
ORDER BY D_CUM desc;
The above query take 2 seconds to return result
Query B
SELECT TOP 15 * FROM CMD4 AS T1 WHERE T1.ST IN
(SELECT max(T2.ST) FROM CMD4 AS T2 WHERE T1.SERIAL=T2.SERIAL
AND T2.sd='20060817') AND sd='20060817' AND STOCKNAME like 'A%' ORDER BY D_CUM desc
This query same as above but " AND STOCKNAME like 'A%'" Criteria only added but it takes 01 minute 43 seconds to return result. why it is like that where i need to adjust the query i have no idea. please help
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
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
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?
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!
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.
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?
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')
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
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?
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?
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.
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
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));
Excuse me in advance fo my little English.I've got this stored procedure************************************************** ************************************declare @Azienda as varchar(3), @Utente as varchar(20),@DataDa as datetime, @DataA as datetime,@AreaDa as varchar(3), @AreaA as varchar(3),@LineaDa as varchar(3), @LineaA as varchar(3),@TipoDa as varchar(3), @TipoA as varchar(3),@FamigliaDa as varchar(3), @FamigliaA as varchar(3),@ProdottoDa as varchar(20), @ProdottoA as varchar(20),@AgenteDa as varchar(4), @AgenteA as varchar(4),@NazioneDa as varchar(50), @NazioneA as varchar(50),@ZonaDa as Varchar(3), @ZonaA as Varchar(3),@ProvinciaDa as varchar(2), @ProvinciaA as varchar(2),@ClienteDa as Varchar(12), @ClienteA as Varchar(12),@DestinDa as varchar (5), @DestinA as varchar (5),@TipoDestinDa as varchar(1), @TipoDestinA as varchar(1),@FlagProdNoTarget as varchar(5),@GrAcqDa as varchar(10), @GrAcqA as varchar(10),@TipoCliDa as varchar(3), @TipoCliA as varchar(3),@SettMercDa as varchar(3), @SettMercA as varchar(3)Set @Azienda = '900'Set @Utente = 'Eugenio'Set @DataDa = '2004-01-01'Set @DataA = '2004-01-10'Set @AreaDa = 'UNI'Set @AreaA = 'UNI'Set @LineaDa = ''Set @LineaA = 'ZZZ'Set @TipoDa = ''Set @TipoA = 'ZZZ'Set @FamigliaDa = ''Set @FamigliaA = 'ZZZ'Set @ProdottoDa = ''Set @ProdottoA = 'ZZZZZZZZZZZZZZZZZZZZ'Set @AgenteDa = ''Set @AgenteA = 'ZZZZ'Set @NazioneDa = ''Set @NazioneA = 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZ'Set @ZonaDa = ''Set @ZonaA = 'ZZZ'Set @ProvinciaDa = ''Set @ProvinciaA = 'ZZ'Set @ClienteDa = ''Set @ClienteA = 'ZZZZZZZZZZZZ'Set @DestinDa = ''Set @DestinA = 'ZZZZZ'Set @TipoDestinDa = ''Set @TipoDestinA = 'Z'Set @FlagProdNoTarget = 'Vero'Set @GrAcqDa = ''Set @GrAcqA = 'ZZZZZZZZZZ'Set @TipoCliDa = ''Set @TipoCliA = 'ZZZ'Set @SettMercDa = ''Set @SettMercA = 'ZZZ'Select WSDFR.AreaCommerciale,WSDFR.Agente,WSDFR.NazDestin,WSDFR.ZonaDestin,WSDFR.ProvDestin,WSDFR.Cliente,WSDFR.DescrCliente,WSDFR.GruppoAcq,WSDFR.TipoCli,WSDFR.SettMerc,WSDFR.CDestin,WSDFR.DescrDestin,WSDFR.TipoDestin,WSDFR.EsclStatis,WSDFR.EsclTarget,WSDFR.ValoreNetto,WSDFR.TpDocum,WSDFR.VCambioITL,WSDFR.VCambioEUR,WSDFR.MeseFatt,WSDFR.PosizioneFrom W_St_DocFatt_Righe WSDFRinner join UniP_Prodotti UPP onWSDFR.prodotto=UPP.CodWhere WSDFR.Dtdocum between @DataDa and @DataA andWSDFR.AreaCommerciale between @AreaDa and @AreaA andWSDFR.LineaProdotto between @LineaDa and @LineaA andWSDFR.TipoProdotto between @TipoDa and @TipoA andWSDFR.FamigliaProdotto between @FamigliaDa and @FamigliaA andWSDFR.Prodotto between @ProdottoDa and @ProdottoA andWSDFR.Agente between @AgenteDa and @AgenteA************************************************** **************************************************"W_St_DocFatt_Righe" is a view.This query run on my SQL7 server and it takes about 10 seconds.This query exists on another SQL7 server and until last week it took about10 seconds.The configuration of both servers are same. Only the hardware is different.Now, on the second server this query takes about 30 minutes to extract the same details, but anybody has changed any details.If I execute this query without Where, it'll show me the details in 7seconds.This query still takes about same time if Where isWhere WSDFR.Dtdocum between @DataDa and @DataA andWSDFR.AreaCommerciale between @AreaDa and @AreaA andWSDFR.LineaProdotto between @LineaDa and @LineaA and--WSDFR.TipoProdotto between @TipoDa and @TipoA and--WSDFR.FamigliaProdotto between @FamigliaDa and @FamigliaA andWSDFR.Prodotto between @ProdottoDa and @ProdottoA andWSDFR.Agente between @AgenteDa and @AgenteAorWhere WSDFR.Dtdocum between @DataDa and @DataA andWSDFR.AreaCommerciale between @AreaDa and @AreaA and--WSDFR.LineaProdotto between @LineaDa and @LineaA and--WSDFR.TipoProdotto between @TipoDa and @TipoA andWSDFR.FamigliaProdotto between @FamigliaDa and @FamigliaA andWSDFR.Prodotto between @ProdottoDa and @ProdottoA andWSDFR.Agente between @AgenteDa and @AgenteAorWhere WSDFR.Dtdocum between @DataDa and @DataA andWSDFR.AreaCommerciale between @AreaDa and @AreaA and--WSDFR.LineaProdotto between @LineaDa and @LineaA and--WSDFR.TipoProdotto between @TipoDa and @TipoA and--WSDFR.FamigliaProdotto between @FamigliaDa and @FamigliaA and--WSDFR.Prodotto between @ProdottoDa and @ProdottoA andWSDFR.Agente between @AgenteDa and @AgenteAIt is a real puzzle!What happen?Is there someone that had such as problems and have the right solution?Thanks in advance.ByeEugenio
Hi all.I want to use the following query in a sp to enable paging using ObjectDataSource.The problem (being EXTREMELEY slow) arises when I add these joins and where statements. SELECT r.RID AS ReqID, r.Name AS ReqName, r.Family AS ReqFamily,t3.Name AS DistName, t4.Name AS RurName,t5.Name AS VilName, n.Name+' '+n.Family AS NazerName ,ROW_NUMBER() over (order by r.Family) AS RowRankFROM Requests rLEFT OUTER JOIN Nazeran n ON r.nazerID = n.ID LEFT OUTER JOIN t1States t1 ON t1.ID = r.StateID LEFT OUTER JOIN t2Provinces t2 ON t1.ID = t2.StateID AND r.ProvID = t2.ID LEFT OUTER JOIN t3Districts t3 ON t2.ID = t3.provID AND t1.ID = t3.stateID AND r.DistID = t3.ID LEFT OUTER JOIN t4RuralDistricts t4 ON t3.ID = t4.distID AND t2.ID = t4.provID AND t1.ID = t4.stateID AND r.RurID = t4.ID LEFT OUTER JOIN t5Villages t5 ON t4.ID = t5.rurID AND t3.ID = t5.distID AND t2.ID = t5.provID AND t1.ID = t5.stateID AND r.VilID = t5.IDWHERE r.stateid=(case when @StateID is null or @StateID='' then r.stateid else @StateID end) and r.provid=(case when @provID is null or @provID='' then r.provid else @provID end) and r.rID=(case when @ReqID is null or @ReqID='' then r.rID else @ReqID end) and isnull(r.nazerID,'')=(case when @nazerID is null or @nazerID='' then isnull(r.nazerID,'') else @nazerID end) and r.name+' '+r.family like (case when @ReqName is null or @ReqName='' then r.name+' '+r.family else '%'+@ReqName+'%' end) **there are 1million rows in [Requests] table ,200000 rows in [t5villages], and about total 5000 rows in other tables.As you can see, this is for a GridView showing list of people requesting a loan allowing users to make alternative searches based on Name, Familyname ,ID ,... would you please help me optimize and make fast this query.Many thanks..
Hai i use Sql Server 2000 today i got this problem when i execute "select * from service_db" some times it is executed successfully but most of times the query on execution continusly why this happen how to solve this pbm very urgent
I have sql query to search for fields in a rather big view. If I execute the query in sql server enterprise manager, the results will be displayed in less than 6 seconds. However, if I execute it using asp.net, it will take very long (more than 2 minutes).
The query is a simple one like "SELECT * FROM myview WHERE name LIKE '%Microsoft%'". And the code I use to execute it in asp.net is
Dim dsRtn As DataSet Dim objConnection As OleDbConnection Try objConnection = GetOleDbConnection() objConnection.Open() Dim objDataAdapter As New OleDbDataAdapter(strSearch, objConnection) Dim objDataSet As New DataSet() objDataAdapter.Fill(objDataSet, strTableName) dsRtn = objDataSet Catch ex As Exception dsRtn = Nothing Finally If objConnection.State = ConnectionState.Open Then objConnection.Close() End If End Try
Where strSearch is the sql search string.
I don't have any problem using such code for other queries.
Could somebody suggest the cause of the problem and how to solve it? Thanks!
I am having a query where I am connecting to eight different tables using joins. When I join one table to another the speed of the execution becomes less. Even on my local server it is taking nearly 2 to 3 minutes to execute the query. How can I increase the speed of execution of my query.
This queries performance is acceptable (about 1 second) when run like this:
SELECT a.f1,a.f2,b.ff1,b.ff2 FROM table1 a LEFT OUTER JOIN table2 b ON a.id = b.id AND b.ff3 = 'T' WHERE a.mydate BETWEEN '3/4/2002' AND '3/6/2002' AND b.ff4 = 'somevalue'
It is terrible (60 seconds) when run like this: SELECT a.f1,a.f2,b.ff1,b.ff2 FROM table1 a LEFT OUTER JOIN table2 b ON a.id = b.id AND b.ff3 = 'T' WHERE b.mydate BETWEEN '3/4/2002' AND '3/6/2002' AND b.ff4 = 'somevalue'
I need the date range to come from the b.maydate. The field is indexed. If I run another query directy on table2 b without a JOIN and using b.mydate for some daterange it is quite fast. Any idea how to speed this up?
Please help me out: It is textremely slow when I run a query in My SQL Server 2000 Query Analyzer on my laptop. But when I turn off the wireless card on the laptop, the query runs instantly. Could you please tell me how can I make the server running faster when my computer is connected to the internet?
