Last week we've made some modifications to our 20 GB database(with 2 huge tables (around 30 million rows)):
1.
we've created 8 filegroups on 8 physical disks with RAID 1+0. In each
of them we have one big table and others small tables unrelated with
the big table. We've also created a filegroup for the nonclustered
indexes from those big tables.
2. The Server is running SQL Server 2000 with SP3a
3. We've reindexed the database, updated statistics on all tables
Now, we have a SP that on the old configuration took about 20 min to complete.
But now, on our new configuration with the modifications listed above it's taking about
3 HOURS.
This is the SP's body: is calling a function:
create proc X
as
begin
Select dbo.myfunc(mytable.field)
from mytable
end
The function used in select statement is querying those 2 huge tables.
If the select statement is runned for only one record it's done instantly, but for 20000 recs it is taking almost 3 hours.
I must specify that also the function is running instantly, but in that select statement something is happening and we don't know what!!!
If you have any ideeas on what to do or what to check
Thanks a lot
Dan
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 query as follow: SELECT @resRate = (SELECT resRate FROM ProjectAssign WHERE proNo = (SELECT projNo FROM Timer_Cust WHERE refNum = @actProjNo AND username= (SELECT username FROM Timer_Emp WHERE refNum = @actEmployee) ) ) the definition of table Timer_cust contains refNum(int), projNo(varchar) and projName(varchar).Timer_cust table doesn't contain column username. So this query shoud generate a runtime error. however it works fine without error.But if I run SELECT projNo FROM Timer_Cust WHERE refNum = @actProjNo AND username= (SELECT username FROM Timer_Emp WHERE refNum = @actEmployee)A runtime error message appears.Why?
hi there , i'm using sql server 2005 express and i have some problems with the two of my tables :), iwant to query both of my tables but the table entries are not in an equal count of rows for example:
incoming(table) outgoing(table)
money date corp money date corp 15 1,1,2006 ar 17 1,1,2006 ar 25 1,2,2007 ar 21 2,2,2007 es 35 2,2,2007 es 6 3,3,2007 ar
in this example the first table has more rows but the opposite is possible in my tables because this is an (account extract) query and here's my problem: first i used the "select incoming.money as m1,incoming date as d1,incoming.corp as c1,outgoing.money as m2,outgoing.date as d2,outgoing.corp as c2 from incoming left outer join outgoing on incoming.corp=outgoing.corp where incoming.corp='ar'(or don't use the where eliminating)" any way i couldn't get the correct results, then i used the (union all) select , the result was ok but the results form is not what i want here's the form i want and is it possible to query to tables to get this? in this query i just wanted the elements with the corporation named 'ar' for example:
m1 m2 d1 d2 c1 c2
15 17 1,1,2006 1,1,2006 ar ar 25 null(or (0)) 1,2,2007 6 null(or (0)) 3,3,2007
is it possible to achieve this form(and may be any of the tables may have more rows than the other, it's not exact) , i am in a very difficult position :) and now i need your help , thanks anybody to think of helping me
select * from jobmaster where status in ('Active') and materialtypecode in (1,2) and unit='IMPERIAL' and superusercode='S051000014' order by controllercode,jobname
through ADO I got the error message:
Invalid column name controllercode
When I execute the same above query in MS SQL Query Analyzer it is working.
I am having in writing a simple select command........here's My ProblemI am using the ASP SQLDataSources in VS2005.... and my need is that i need to show Products details in a Gridview...... Actually through QueryString a StoreID is being fetched..... and the Products under those StoreID are shown..... if there is nothing in query string then it should show all the results... ........ It means that my querystring should be something like thisselect * from tblProducts where StoreID = <xyz>and <xyz> should be some thing that could show all the rows in that table ........ i mean that it should show all the possible results that can be shown through...select * from tblProducts
I have two pages that Im using the same query out of... one page returns results, the other page returns an error... SOMEBODY HELP!!! I just need a hint if nothing more!!!
The Error: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'WHERE'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Global.Default_Search(Object Sender, EventArgs e) in C:InetpubwwwrootChampionRealtyINFOprojecttrackingglobal.vb:line 206
The SQL Query:
SELECT PT_JobCat.JobCat, PT_Job.JobID, PT_Job.Created, PT_Status.Status, Offices.Name, Employees.First + ' ' + Employees.Last AS EmpName FROM PT_Job INNER JOIN PT_JobCat ON PT_Job.CatID = PT_JobCat.CatID INNER JOIN PT_Status ON PT_Job.StatusID = PT_Status.StatusID INNER JOIN PT_Assign ON PT_Job.JobID = PT_Assign.JobID INNER JOIN Employees ON PT_Assign.AgentID = Employees.ID INNER JOIN PT_Office ON PT_Job.JobID = PT_Office.JobID INNER JOIN Offices ON PT_Office.Office_ID = Offices.ID WHERE PT_Job.JobID=1
The SQL Query Output By ASP.NET: (When an error is produced on the trouble page)
SELECT PT_JobCat.JobCat, PT_Job.JobID, PT_Job.Created, PT_Status.Status, Offices.Name AS OfficeName, Employees.First + ' ' + Employees.Last AS EmpName FROM PT_Job INNER JOIN PT_JobCat ON PT_Job.CatID = PT_JobCat.CatID INNER JOIN PT_Status ON PT_Job.StatusID = PT_Status.StatusID INNER JOIN PT_Assign ON PT_Job.JobID = PT_Assign.JobID INNER JOIN Employees ON PT_Assign.AgentID = Employees.ID INNER JOIN PT_Office ON PT_Job.JobID = PT_Office.JobID INNER JOIN Offices ON PT_Office.Office_ID = Offices.ID WHERE JobID=1
The Trouble Page:
Public Sub Default_Search(ByVal Sender As Object, ByVal e As EventArgs) dim NeedAnd as boolean = False dim strSQL as string = "SELECT PT_JobCat.JobCat, PT_Job.JobID, PT_Job.Created, PT_Status.Status, " & _ "Offices.Name AS OfficeName, Employees.First + ' ' + Employees.Last AS EmpName " & _ "FROM PT_Job " & _ "INNER JOIN PT_JobCat ON PT_Job.CatID = PT_JobCat.CatID " & _ "INNER JOIN PT_Status ON PT_Job.StatusID = PT_Status.StatusID " & _ "INNER JOIN PT_Assign ON PT_Job.JobID = PT_Assign.JobID " & _ "INNER JOIN Employees ON PT_Assign.AgentID = Employees.ID " & _ "INNER JOIN PT_Office ON PT_Job.JobID = PT_Office.JobID " & _ "INNER JOIN Offices ON PT_Office.Office_ID = Offices.ID " & _ "WHERE " dim C as New SQLCommand(strSQL, Conn) dim DR as SQLDataReader If txtJobNum.Text <> Nothing Then strSQL += "JobID=" & CInt(txtJobNum.Text) & " " NeedAnd = True End If If ddlJobType.SelectedItem.Value <> Nothing Then If NeedAnd Then strSQL += "AND " strSQL += "CatID=" & ddlJobType.SelectedItem.Value & " " NeedAnd = True End If If txtCreated.Text <> Nothing Then If NeedAnd Then strSQL += "AND " strSQL += "PT_Job.Created LIKE '" & txtCreated.Text & "' " NeedAnd = True End If If ddlStatus.SelectedItem.Value <> Nothing Then If NeedAnd Then strSQL += "AND " strSQL += "PT_Job.Status=" & ddlStatus.SelectedItem.Value & " " NeedAnd = True End If If ddlAssign.SelectedItem.Value <> Nothing Then If NeedAnd Then strSQL += "AND " strSQL += "Employees.ID=" & ddlAssign.SelectedItem.Value & " " NeedAnd = True End If If ddlOffice.SelectedItem.Value <> Nothing Then If NeedAnd Then strSQL += "AND " strSQL += "Offices.ID=" & ddlOffice.SelectedItem.Value & " " NeedAnd = True End If
response.write(strSQL)
Conn.Open Try DR = C.ExecuteReader If DR.Read Then rptResults.Datasource = DR rptResults.Databind End If DR.Close Catch Exc as Exception 'ErrorAlert(Exc, strSQL) response.write("<p>" & Exc.ToString.Replace(Environment.NewLine(), "<br />") & "</p>") End Try Conn.Close
End Sub
The Page That Works:
Sub LoadResults(ByVal Sender As Object, ByVal e As EventArgs)
' Clean up some things... txtError.Visible = False txtError.Text = Nothing
' Create database interaction objects... dim Conn as Object dim C as Object dim DR
' Set database interaction objects... Select Case CInt(DBType.SelectedItem.Value) Case 0 Conn = New SQLConnection(txtConnString.Text) C = New SQLCommand(txtSQL.Text, Conn) Case 1 Conn = New OleDbConnection(txtConnString.Text) C = New OleDbCommand(txtSQL.Text, Conn) Case Else txtError.Visible = True txtError.Text = "Whoa... wierd error d00d... o.0" End Select
' Open the database for reading... Conn.Open
Try
' Load the datagrid with any information retrieved... Select Case CInt(CommType.SelectedItem.Value) Case 0 DR = C.ExecuteReader() dgResults.Datasource = DR dgResults.Databind DR.Close Case 1 C.ExecuteNonQuery() Case Else txtError.Visible = True txtError.Text = "That's some funky ****..." End Select
Catch Exc As Exception
' Make the error viewable so we know what went on... txtError.Visible = True txtError.Text = Exc.ToString
End Try
' Close the connection to the database... Conn.Close
First off, let me get my disclaimer out of the way - I'm a relative newbie at this SQL lark, so please ignore the kludgy-ness of this code etc. Thanks ;-)
DECLARE issue Cursor scroll FOR -- Create the cursor select articleIssue, articleDate from cr_newsletterArticles where newsletter = @newsletter -- Which Newsletter order by articleIssue desc ;
open issue -- Open the cursor SET @previssue = 0 -- Set our comparison variable to 0
FETCH next FROM issue -- Grab the first recordset from the cursor into @issuecounter, @date -- Stick it into our variable WHILE @@fetch_status = 0 -- While we're not at EOF begin -- begin if @issuecounter != @previssue BEGIN -- begin insert into @issues(issues,dates) values(@issuecounter, @date) SET @previssue = @issuecounter -- set comparison variable to issuecounter END fetch next from issue -- Grab the next recordset into @issuecounter, @date END -- End CLOSE issue -- close cursor deallocate issue select * from @issues GO
When I run this query in Query Analyser, it seems to work fine. IE. I get a table returned with the correct values. However, when I try and get this data into ASP via the old objRS.open "EXECUTE stored procedure <value>" command, no data is returned...
I'm not sure if this is an ASP problem, or a SQL problem... So, I altered the query slightly, and instead of a temporary table, I used a static one in the database, and it still didn't work - even though when I open the table in Enterprise manager the content is there...
Also, if I run the query via an ASP objCommand.execute "procedure <value> " and then use objRS.open "SELECT * from table" then it pulls the data in properly then...
If I was dealing with millions of recordsets in my original table, then I can understand that maybe the ASP isn't waiting long enough for SQL to return its final table. But I'm currently only dealing with about 50... I'd rather use the temporary table route rather than static incase of multiple hits at the procedure at the same time...
Any ideas? Please go easy - I refer you back to my disclaimer ;-)
I have a query like below .. if i add where Served = 1 , the query takes foreever... if i remove it, it takes only 6 sec...
I am not sure why this is hapening?
select distinct a.Episode_Key, case when ag.Category IN ('ASMI', 'COOC', 'SPCL') then 'SMI' when ag.Category = 'SEDC' then 'SED' when ag.Category = 'ACCA' then 'SA' when ag.Category like 'CGA%' then 'Gam' end as [Category], ag.Agreement_Type_Name as [Agreement], p.ServiceProvider, s2.Served from dbo.Assessment a INNER JOIN ( select distinct Episode_Key, p.ServiceProvider, max(CSDS_Object_Key) as [Sequence] from dbo.Assessment a INNER JOIN dbo.CD_Provider_Xref p ON a.Provider_CD = p.Provider_CD where Creation_DT >= '07/01/2007' and Reason_CD = 1 group by Episode_Key, p.ServiceProvider ) as s1 ON a.CSDS_Object_Key = s1.Sequence INNER JOIN dbo.CD_Provider_XREF p ON a.Provider_CD = p.Provider_CD INNER JOIN dbo.CD_Agreement_Type ag ON ag.Agreement_Type_CD = a.Agreement_Type_CD LEFT OUTER JOIN ( select distinct Episode_Key, p.ServiceProvider, 1 as [Served] from dbo.Encounters e INNER JOIN dbo.CD_Provider_Xref p ON e.Provider_CD = p.Provider_CD where Encounter_Begin_DT between '01/01/2008' and '01/31/2008' and Procedure_CD is not null and Encounter_Units > 0 ) as s2 ON a.Episode_Key = s2.Episode_Key and p.ServiceProvider = s2.ServiceProvider ????---where Served = 1 group by a.Episode_Key, ag.Agreement_Type_Name, p.ServiceProvider, Served, case when ag.Category IN ('ASMI', 'COOC', 'SPCL') then 'SMI' when ag.Category = 'SEDC' then 'SED' when ag.Category = 'ACCA' then 'SA' when ag.Category like 'CGA%' then 'Gam' End
Hi all,Using SQL Server 2000, SP4.I have a table of street names (Rua) whose ids (cod_rua) are foreignkeys into a consumer table (Consumidor). It turns out that the "Rua"table has many unused records which I'd like to wipe out. For instance,there are some 2800 unused records in the "Rua" table, and only some200 records actually being used by the "Consumidor" table (which,itself, has some 5000 records).Attempting to find the unused records, I issued the following query:a)SELECT COD_RUA FROM RUAWHERE COD_RUA NOT IN (SELECT COD_RUA FROM CONSUMIDOR)To my surprise, the query came out empty. But the following queryshowed the 200 or so records which *are* being used:b)SELECT COD_RUA FROM RUAWHERE COD_RUA IN (SELECT COD_RUA FROM CONSUMIDOR)I've found two solutions for the query to list the records *notexisting* in the Consumidor table:c)SELECT COD_RUA FROM RUAWHERE COD_RUA NOT IN (SELECT COD_RUA FROM CONSUMIDORWHERE COD_RUA IS NOT NULL)d)SELECT COD_RUA FROM RUAWHERE COD_RUA NOT IN (SELECT COD_RUA FROM RUAWHERE COD_RUA IN (SELECT COD_RUA FROM CONSUMIDOR))I know that there are many other possible solutions to the query(including left joins), but what I don't understand is why the query a)failed.Can some of you, oh mighty gurus, enlighten me?For the record, here's how both tables are (partially) declared:CREATE TABLE Rua (Cod_Rua int NOT NULL ,Rua varchar (35) NULL ,-- ...-- other unrelated fields-- ...CONSTRAINT Pk_CodRuaPRIMARY KEY (Cod_Rua))CREATE TABLE Consumidor (Cod_Consumidor int NOT NULL ,Cod_Rua int NULL ,-- ...-- other unrelated fields-- ...CONSTRAINT Pk_CodConsumidorPRIMARY KEY(Cod_Consumidor) ,CONSTRAINT Fk_CodRua_ConsumidorFOREIGN KEY (Cod_Rua)REFERENCES Rua (Cod_Rua))Regards,Branco Medeiros
Hi everibody,it's the first time i post on this newsgroup. I'm Stefano from Milano,italy.I'm a beginners with Sql2000. My problem is this.I run a View using enterprise manager and after less then 20 second it goesin error time out. I run this view using a VB application and the errorcomes again .When i run it with Query Analyzer after 50 seconds it give methe right result.i've tried to change the value of querytimeout using sp_configure with thesame bad result.i've tried to change the ado command timeout in visul basic but stilldoesn't work.any suggest ?Thanks in advance
I am seeming strange results with a query. I have two tables, lets call them Table1 and Table2. Table1 has an ID field, Table2 does not have an ID field. To be sure I wasn't blind, the query
'SELECT ID FROM Table2'
returns: Invalid column name 'ID'. OK. Now when I run the query
'SELECT * FROM Table1 WHERE ID IN (SELECT ID FROM Table2)'
it returns all the records from Table1.
What gives? Is this a bug, or am I missing something?
Jezemine, No, the number of reads is approximately the same. I can also confirm the disk read speed is the same on the test vs. production server. Update stats is run regularly on the production server - as I test, I ran sp_updatestats and then immediately ran the query a few times but it didn't affect the duration. Apart from the durations in the profiler traces, I can't see any differences. Clearly, something is causing the increased duration on the prod server but I don't know where to look to find it. It's definitely within SQL Server 2000.
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
I have been trying to get this code work, but I could not. Every thing seems going well. However, The result of running the sql query is strange. It shows the field names twice. Eg:) if you have a table called "newtable" that has two fields[Custnumber, Custname], you will get somthing like this [Custnumber, Custname Custnumber, Custname]. I have tried many times, but I couldn't fix it.
Sub Page_Load(sender As Object, e As EventArgs) handles Mybase.Load
if not page.Ispostback then
try Sqlconnection = New Sqlconnection (connectionString)
querystring = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNs WHERE TABLE_NAME = 'Newtable'"
SqlCommand = New SqlCommand(queryString, Sqlconnection)
We have SQL server 7 installed. And we experiencing the performance problem. When I tried to solve this problem I have found the interesting thing: We have the table S_EVT_ACT with the non clustered index S_EVT_ACT_F4 created on [OWNER_PER_ID], [APPT_REPT_FLG] fields and another clustered one S_EVT_ACT_M4 created on [ROW_STATUS], [OWNER_PER_ID] fields I use the next select statement and before run this statement I declare the variable @P1 - @P5 and set the values to them:
declare @P1 as char(1) declare @P2 as char(1) declare @P3 as char(1) declare @P4 as varchar(10) declare @P5 as char(1)
set @P1 = 'Y' set @P2 = 'Y' set @P3 = 'N' set @P4 = '1-K56' set @P5 = 'Y'
SELECT .. ... ... FROM dbo.S_EVT_ACT T1 --(index = S_EVT_ACT_F4) LEFT OUTER JOIN dbo.S_CONTACT T2 ON T1.TARGET_PER_ID = T2.ROW_ID LEFT OUTER JOIN dbo.S_OPTY T3 ON T1.OPTY_ID = T3.ROW_ID LEFT OUTER JOIN dbo.S_ORG_EXT T4 ON T1.TARGET_OU_ID = T4.ROW_ID LEFT OUTER JOIN dbo.S_EVT_ACT_X T5 ON T1.ROW_ID = T5.PAR_ROW_ID WHERE ((T1.ALARM_FLAG = @P1 OR T1.APPT_REPT_REPL_CD IS NOT NULL) AND (T1.APPT_REPT_FLG = @P2 AND (T1.CAL_DISP_FLG = @P3 OR T1.CAL_DISP_FLG IS NULL)) AND (T1.OWNER_PER_ID = @P4) AND (T1.TEMPLATE_FLG != @P5 OR T1.TEMPLATE_FLG IS NULL)) ORDER BY T1.CREATED
In this case Query Analyzer uses S_EVT_ACT_M4 index and the performance is bad!
But when I try to run the same statement and use hard code 'Y' instead of the variable @P2 the Query Analyzer uses the S_EVT_ACT_F4 index and performance is PERFECT.
Question: What the difference between using variable @P2 and hard code 'Y' in the select statement and how to configure SQL server to use the right index in the situation when I can't change the Select statement and use the hard coding?
Any Idea will be very Appreciated.
Dmitri Denejkine stssystems MS SQL Server DBA, MIS (514) 426-0822 ext. 2676
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
