I have a website www.searchcontracts.com which has a search feature on the front page. The problem is that whenever a search is submitted (without and filters) it processes extremely slow (if at all, sometime throws 'general network' errors). If working properly it will return about 500+ records. As far as i know if there should be no issues returning a recordset of this size. I have the site hosted in a shared environment and it uses a SQL2000 db. If you filter the search resutls, it's more reponsive (still fairly slow).
If you believe that the hosting server i'm using should be upgraded, what would be the best solution? Keep in mind that i want to be able to return way more than 500+ records in one go.
I have 2 servers (say MAINSRV e SECSRV) running SQL2000 Standard SP3 on Windows 2000 Advanced within a NT (!) domain and each server is linked to the other.
My problem is that if I run a query returning few dozens of rows like:
SELECT * FROM MAINSRV.DbName.dbo.TblName TBLA WHERE Fieldx = 'anyval'
from a client connected to the SECSRV server, it takes something like 35 minutes to complete, while the same query completes in no time when run on clients connected to MAINSRV.
Even the simplest SELECT Count(*) FROM... takes more than one minute from SECSRV while completing in a fraction of second from MAINSRV.
I tried to change the linked server security options (SQL/Windows), but the remote query remains slow.
There are no locks active on the table, both the servers have almost no load (CPU less than 10%, when tested) and the query returns just a few KBytes, so communication overhead will not be the problem.
Any suggestions will be very appreciated, thank you!!!
Hello.I have a slow response with a system that I am setting up. The OS isWin 2000 Server with SQL Server 2000. My first execution of the SQLprocedure is slow (about 40 seconds), while the second execution isfaster (approx. 3 seconds and what I expect).Using query analyser I can leave the window up and come back ( aftersay 10 minutes ) and my next response is will be slow (and fasterstraight after this).The query itself is the "TOP 10" rows from a table of a remote DB (MSDE2000 - NT station).This is similar to;---------------------DECLARE @SelectRowsFromRemoteDB NVARCHAR(400)SET @SelectRowsFromRemoteDB = "SELCT a.* FROM OPENROWSET('SQLOLEDB', 'Remote', ....) a"EXEC sp_executesql @SelectRowsFromRemoteDB---------------------The CPU usage goes high (with Task Manager), but oddly the task withmost usage is the "Idle Process".Please help with an answeror a good starting point to known where the resources are being used.Regards JC.....
Hi I have Problem, My response time is too Low. Is Any one Know how to improve my response time. My DATABASE SIZE IS 11 GB. I didn’t change any configuration parameter after installing SQL Server. Right my server Having default configuration parameters. Whether I have to change any parameters or not. My queries will generate lot of temporary tables.
At my location we are running Great Plains on SQL server with the Great Plains client on the front end communicating over IP Sockets. Occasionally the client will stop responding for up to 15 minutes. This occurs to all clients at the same time. While this is happening I have ran performance monitor on the SQL server using the SQL counters and everything looks fine. Performance on the server and the network does not degrade. Only the Great Plains client is affected. From this I feel this is not a network problem. I would like some suggestions on what could possible cause this type of problem. I am really just looking for a starting point on where the problem may be originating from. Any suggestions will be greatly appreciated.
Here is the configuration we have at our site: 64 bit virtual server - Clustered 64-bit SQL Server 2005 Standard SP2 listening on port 1433.
32 bit virtual server - 32-bit application that connects to above SQL Server 2005 via standard OLEDB driver installed with windows (String utilized: Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=applicationdb;Data Source=ServerInstanceName).
We are experiencing a slow reponse of 45 sec to more than a minute on a simple select statement on a table having 5000 rows.
The same select statement on the Server locally in Studio returns results back in 2 seconds or less. But from Stuido on any other desktop (remote) it takes 45 secs or more. Same slow response is seen by the application which is using OLEDB.
I've looked into some of the Server parameters but didn't find any clues.
I ran into a situation that if I don't use sql server for a while, in MS SQL Server Management Studio, when I run some query again, it takes a long time to respond. I looked into it with Activity Monitor, seems like the query was waiting for something, in the process view tab, it shows that: WaitType: LATCH_EX Resource: LOG_MANAGER(112F88C8) What is the latch-ex? What should I check next to find out the problem?
We did an in place convertion of our data base from MS SQL Server 6.5 to 7.0. Our application is much slower now on SQL 7.0. Any idea why? The following is a sample SQL statement that runs quickly on SQL 6.5 and takes a long time on SQL 7.0 I also attached the query plans from SQL 6.5 and 7.0.
SELECT Person_Name.PerNam_Person_Name_PK , Person_Name.PerNam_Row_Status , Person_Name.PerNam_Last_Name_Sndx , Person_Name.PerNam_Last_Name , Person_Name.PerNam_Name_Suffix , Person_Name.PerNam_First_Name , Person_Name.PerNam_Name_Prefix , Person_Name.PerNam_Middle_Name , Person_Name.PerNam_Event_Person_FK , Event.Evn_Event_Nbr , Event.Evn_Event_Type , Event_Person.EvnPer_Last_Name , Event_Person.EvnPer_First_Name , Event_Person.EvnPer_Middle_Name , Event_Person.EvnPer_Name_Prefix , Event_Person.EvnPer_Name_Suffix FROM Person_Name , Event , Event_Person WHERE (Person_Name.PerNam_Agency_ID = "CL") AND ( Person_Name.PerNam_Event_Person_FK = Event_Person.EvnPer_Event_Person_PK ) and ( Event_Person.EvnPer_Event_FK = Event.Evn_Event_PK ) and (Person_Name.PerNam_Person_Name_PK = 0 or ( Person_Name.PerNam_Event_Person_FK = 581541) ) and ( Person_Name.PerNam_Row_Status <> "D" )
Query plan in SQL 6.5
SQL Server Execution Times: cpu time = 0 ms. elapsed time = 31250 ms. STEP 1 The type of query is INSERT The update mode is direct Worktable created for REFORMATTING FROM TABLE Person_Name Nested iteration Index : PK_Person_Name FROM TABLE Person_Name Nested iteration Index : PerNam_Event_Person_FK FROM TABLE Person_Name Nested iteration Using Dynamic Index FROM TABLE Event_Person Nested iteration Index : PK_Event_Person TO TABLE Worktable 1 STEP 2 The type of query is SELECT FROM TABLE Worktable 1 Nested iteration Table Scan FROM TABLE Event Nested iteration Index : PK_Event SQL Server Parse and Compile Time: cpu time = 0 ms. Table: Person_Name scan count 2, logical reads: 6, physical reads: 5, read ahead reads: 0 Table: Event scan count 0, logical reads: 0, physical reads: 0, read ahead reads: 0 Table: Event_Person scan count 0, logical reads: 0, physical reads: 0, read ahead reads: 0 Table: Worktable scan count 0, logical reads: 0, physical reads: 0, read ahead reads: 0 Table: Worktable scan count 1, logical reads: 1, physical reads: 0, read ahead reads: 0
SQL Server Execution Times: cpu time = 0 ms. elapsed time = 62 ms.
I am working with a client that after every reboot of there SQL 2000 DB server, they experience slow response time for a couple of hours. The server has 12 GB RAM and a Dual 3.8 processor. It is believed that the slow response is due to as queries run after the reboot, they are re-building information in memory and after the memory is built up, it goes back to the normal performance utilizing the memory for speed. Is this an accurate assumption or is there something else to be looking at after the server is rebooted?
Using SSMS 2012, we are experiencing extremely slow response times when opening SQL job steps to edit and also deploying SSIS Pkg's. Sysadmins have no problem. Users in the ssis_admin role have no problem. It's the rest of the users who have issues.
I have a problem with querying systemjobhistory data. Response time is slow and it is vary from time to time, sometime it takes few seconds and sometime it takes more than 2 minutes. I understand that there is quite a number of jobs in DB server and which might result in slow response time.
Is it possible to shorten the response time? like using index? My application is always look like hang when the query take very long time to run.
When I launch Outlook, it takes forever for the program to finally open. With any inbound email, it stops processing whatever is underway at the time....and frequently there is a 2-3 second lag between keyboard input and what appears on the screen. SQLserver is usually consuming upwards of 1-gb of memory....help. Mike
Hi there, I tried to make it different as usual but i´m stacked into this problem:Supose TABLE DetailsID_DET ID_CAR DETAILS1 3 1,2,3,4,5,62 4 2,43 5 5,6,7,8andTABLE Details_ItemsID_DI DETAIL_NAME1 Stereo HiFi CD2 Alarm3 AirConditioning4 LeatherSeats5 Pro Wires6 Aluminium WheelsThe problem appears when i need to bring CAR DETAILS (NAME) from TABLE DETAIL_ITEMS.Mi guess i that I should make something like:SELECT * FROM DETAILS_NAME WHERE id_di = (( Array(i) FROM Details )) one by one...I really dont know how to face it.First I thought in bringing ALL details_Items (datafieldtext = id_di and datavaluetext=Details_names) into a dataview.And then "somohow?" filter this dataview according with the Array previuosly splited by me with a For each function.Then I thought "Perhaps" there is a simpliest way to do that using SQL views, o advanced SQL QUERYS.and Finally I thought that creating a VIEW in for both TABLES would be great.The point is that, neither 1,2,3 options, honestly , I dont know how to face them.Thanks in advance, apologise my "rude" English grammar.LUCAS ( From Argentina )
Is there a global variable or something of the sort that would tell me how long it took to execute a query??
I need to monitor my DB response times and we have a query that runs in under 2 seconds. So we want to run this query every couple of minutes and if it takes more than 12 sec to run, we want to send an email to our DB staff...
I know that I could take a time stamp before and after then subtract but I wanted to know if there was an easier way to do it..
have a problem ... I tried to test my statement in QA as it returns no dataset ,it gives me empty columns in return,I though it will return all the columns and some records of (lmeyer) as the current user ,who logged in on windows auth,but it return only the columns,is that means my query analyser cannot read the login1 ,column which has the username as (lmeyer)
select * from tstudents where (login1='@param1')
if not what could me wrong in my code ,because I get no response to the sql server
Public Function login(ByVal login1 as string) as dataset
Dim myconnection as new sqlconnection("server=G103-TT03;database=CampusLANDB;Trusted_Connection=yes") dim mycommand as new sqlcommand ("Select * from tStudents Where login1 = @param1",myconnection) mycommand.parameters.add(new SQLClient.SqlParameter("@param1",login1))
dim DS as new dataset()
try myconnection.open
dim adpt as new sqldataadapter adpt.selectcommand = mycommand adpt.fill(DS,"tStudents")
catch ex as exception throw new exception(ex.message) return nothing finally myconnection.close end try
try mydatagrid.datasource=DS.Tables("tStudents") mydatagrid.databind() catch ex as exception errorlabel.text=ex.message end try
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 a SQL .bak generated by our existing hosting package at 1and1.com, I want to import that SQL .bak to our new hosting package at ixwebhosting.com.
ixwebhosting.com don't have a import feature in their control panel.
I have to maintain the SQL at ixwebhosting.com using Microsoft SQL Server Management Studio Express since they don't have that feature in their cp. I don't see any import features in Microsoft SQL Server Management Studio Express either.
How can I import it using Microsoft SQL Server Management Studio Express or another free GUI program?
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));
