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 a strange performance question hopefully someone can clarifyfor me. I take a production database and make a copy of it, calledtest, on the same instance on the same server both running at the sametime. All the same things are running for each database and no one isusing each database but me. From Query Analyzer I run a SQL againstproduction three times in a row and it takes 1 minute 40 seconds onthe last/best run. I then run the same SQL against the test copy andrun it three times in a row and the last/best time is 12 seconds. Cananyone explain this behavior? If so, I hope this points to something Ican do to my production database to get the same performance.Thanks,MGB
Hi, I have a strange performance issue. I have the following query which takes 40+ seconds to run. SELECT Count(x) FROM view WHERE x = 347 AND y = 10056 AND z = 2 AND w = '01' But if i switch it to below, the query returns the one result quickly (1 second).SELECT x FROM view WHERE x = 347 AND y = 10056 AND z = 2 AND w = '01' If the view is returning results quickly, why is it so much trouble for SQL to run the aggregate function on the results?Any help is appreciated. -Brian
Hi,I have a really interesting one for you guys...SQL Server 2000 sp3 on Windows Server 2003If I run this query:declare@find varchar(50)SET @find = 'TTLD0006423203'SELECT TOP 250ConsignmentID,c.CreatedFROM tblConsignment c WITH (NOLOCK)WHERE c.ConNoteNum LIKE @find + '%'ORDER BY c.Created DESCIt takes 5 - 7 seconds with an Index Scan on the Consignment TableHOWEVER, if I run either of the next two queries below they are instant(under 1 second) with no scan only an Index Seek ..declare@find2 varchar(50),@SQL nvarchar(4000)SET @find2 = 'TTLD0006423203'SET @SQL = 'SELECT TOP 250ConsignmentID,c.CreatedFROM Tranzbranch_archive.dbo.tblConsignment c WITH (NOLOCK)LEFT JOIN tblCustomer cu WITH (NOLOCK) ON c.FreightPayerCode =cu.CustCodeWHERE c.ConNoteNum LIKE ''' + @find2 + '%''ORDER BY c.Created DESC'execute sp_executesql @stmt = @SQLORSELECT TOP 250ConsignmentID,c.CreatedFROM tranzbranch_archive.dbo.tblConsignment c WITH (NOLOCK)LEFT JOIN tblCustomer cu WITH (NOLOCK) ON c.FreightPayerCode =cu.CustCodeWHERE c.ConNoteNum LIKE 'ttld0006423203%'ORDER BY c.Created DESCCan you please help me as this is causing Huge issues in our Live systemand I really don't want to rewrite 400+ stored procedures!!!!Thank you thank you thank you in advance....:-)Auday*** Sent via Developersdex http://www.developersdex.com ***
I got a strange performance issue with my existing application. The application run fine for a period of time. However, it got timeout error every time now when the number of records have been grown to a certain size.
The program uses Typed DataSet to access SQL Server 2005 database. The connection is made over a VPN. The same program and SQL run in a LAN environment performs not too bad. However, when it is run over the VPN, the CPU and I/O usage jumped to very high numbers. In the SQL Profiler, I found that the duration of execution is less than 20ms in LAN environment while the VPN will give me a figure around 30000ms (This means timeout).
I don't want to simply increase the timeout of my connection and command in order to solve this problem temporary. What's the actual cause of such huge difference in the performance?
I'm using a SQL statement with a CTE to recursively get all node-ids from a tree beginning with a root node. In a select statement I'm using this CTE to get in a SELECT ... WHERE nodeID IN (SELECT ID FROM CTE_Nodes) statement data that is according to the nodes related to the root-node.
In our ASP.NET 2.0 application these statements are very slow or time out with an Exception. When I'm executing the same statement in a Management Studio the statement executes in less than one second.
BTW, we're using SQL Server 2005 Standard Ed. (9.0.3050 + 9.0.3054) in SQL Server 2000 compatibility mode with SQL Authentication.
I'm a bit frustrated, because the statements are the same.
Thanks in advance,
Klaus
Update:
BTW, the SQL Server is on a server machine and the Management Studio and the ASP.NET application on my developer machine. For data access we're using Enterprise Library 2.0 with System.Data.SqlClient.
Which possibilities do we have to trace this issue? Please help.
Hi, I have a SQL Server stored procedure that gets called in the ASP.NET application. For a while it will return results quickly. After an unknown amount of time the stored procedure starts taking forever to execute. If I go into Query Analyzer and execute the same stored procedure using the exact same input parameters the results return quickly. If I then go back into the application and run the code that executes the stored procedure, the results return quickly again. So basically every time the application call to the stored procedure begins to slow, I run that stored procedure in query analyzer and then it runs fine in the application again. Has anyone else experienced anything like this or have any ideas as to why this would happen?
Hello!I have this piece of SQL code:UPDATE aSET Field1 = c.Field1FROM aINNER JOIN b ON a.GUID1 = b.GUID1INNER JOIN c ON b.GUID2 = c.GUID2WHERE c.Type = 1AND @date BETWEEN b.DateFrom AND b.DateToThis query takes hours to complete.Now while trying to find out what's causing the poor performance (itsurely looks simple enough!) I've rewritten it to use temp tables:SELECT a.GUID1, a.Field1, c.Type, b.DateFrom, b.DateTo INTO #temptableFROM aINNER JOIN b ON a.GUID1 = b.GUID1INNER JOIN c ON b.GUID2 = c.GUID2WHERE c.Type = 1AND @date BETWEEN b.DateFrom AND b.DateToUPDATE a SET Field1 = subsel.Field1FROM (SELECT * FROM #temptable) AS subselWHERE subsel.GUID1 = a.GUID1Now it completes in 10 seconds.My question is why? Am I wrong in saying that the two batches aboveproduce same results? Is there something I've missed about the UPDATEFROM syntax? Why would the first query perform THAT poorly?Table sizes:a: 24k rowsb: 268k rowsc: 260k rowsGUIDs are of type uniqueidentifier.Any answers appreciated!Regards,// Richard
Not sure if this question belongs in this area or the server area but I'll start here. Here is my problem. We have an C/C++ app that was originally written for SQL 2000 and uses DBLibrary. We have converted it to SQL 2005 and are using ODBC/Native client to access the SQL 2005 database. This all works great. So we were doing some performance testing and we noticed that our update performance seems slower in the SQL 2005/ODBC case than it did in the SQL 2000/DBLibrary case. Inserts and queries all perform great, in fact the inserts are significantly faster in the SQL 2005/ODBC case which is good. We are using Array inserts/updates/queries wherever possible as this is faster obviously. In our update case, it takes 1.14 seconds to update 2000 rows in table in the SQL 2005/ODBC case, while SQL 2000/DBLibrary case takes .39 seconds to the exact same thing. The table in question is a 12 column table with all integer columns, with an index on the first three columns.
So my question is this. Is there something different about Array Updates in SQL 2005 ? I've looked thru the list of hot fixes available since SQL 2005 SP2 and haven't seen anything that directly mentions Updates so I'm hesitant to go off and start applying the hot fixes to our server to see if the behavior changes. It seems strange to me that Array Inserts would be very fast, but Updates wouldn't be. I've checked the ODBC Data Source and we aren't doing anything fancy there. I'm not actually even sure if this problem is client side or server side as I said earlier.
If anyone has any ideas or thoughts that would be great since this is really bugging me.
Here is a sample of what our C code is doing. This is simplied and I've removed a lot of our own code but these are the SQL calls that we are making in order so maybe can see something wrong I'm doing.
//----Sample Code ------------------------------------------------------------------------------------- pSQL = "Update bob set VV=? where VI=?" // not done this way actually in our code but just to show the update text
status = SQLPrepare ( hS, (UCHAR *)pSQL, SQL_NTS );
// Called twice with nCol = 1 and then with nCol = 2 status = SQLSetParam ( hS, abs(nCol), cType, // cType = 5 = SQL_C_LONG sqlType, // sqlType = 4 = SQL_INTEGER 38, // size needed in case the column is numeric or decimal 0, p16Data, // Pointer to my array of data NULL );
I have a simple stored procedure that send a message on a conversation:
Code Snippet
CREATE PROCEDURE [Vxml].[sp_SendMessageToWarehouse] -- Add the parameters for the stored procedure here @Message XML, @EntityId uniqueidentifier, @ConversationHandle uniqueidentifier OUTPUT AS BEGIN BEGIN TRY ;SEND ON CONVERSATION @ConversationHandle MESSAGE TYPE VxmlEngineXMLMessage(@Message); END TRY BEGIN CATCH INSERT INTO [Vxml].SBError (ErrorMsg) VALUES (N'Error <' + ERROR_MESSAGE() + N'> for entity <' + cast(@EntityId as NVARCHAR(max)) + N'> on conversation <)' + cast(@ConversationHandle as NVARCHAR(max)) + N'> with body ' + cast(@Message as NVARCHAR(max))) END CATCH; END
This completes in less than 100ms (avg 30ms) except once in about 10000 executes (only one simultaneous execute), then this takes about 3 seconds to complete.
When performing the same test and calling the stored procedure simultaneously from 5 to 20 threads, we see up to 3 such spikes (in 10000 executes) taking 2 to 4 seconds.
Conversations are created before the test and closed after. No ODBC errors occur and the SBError table is empty.This is run on an SQLEXPRESS the conversation target is a SQL Server 2005 (standard edition)
Can anyone explain these strange performance spikes ?
Hello everyone, I am hoping someone can help me with this problem. Iwill say up front that I am not a SQL Server DBA, I am a developer. Ihave an application that sends about 25 simultaneous queries to a SQLServer 2000 Standard Edition SP4 running on Windows 2000 Server with2.5 GB of memory. About 11 of these queries are over views (all overthe same table) and these queries are all done from JDBC but I am notsure that matters. Anyway, initially I had no problem with thesequeries on the tables and the views with about 4 years of information(I don't know how many rows off hand). Then we changed the tables toreplicated tables from another server and that increased the amount ofdata to 15 years worth and also required a simple inner join on 2columns to another table for those views.Now here is the issue. After times of inactivity or other times duringthe day with enough time between my test query run I get what lookslike blocking behavior on the queries to the views (remember these allgo to the same tables). I run my 25 queries and the 11 view queriesall take about 120 seconds each to return (they all are withinmilliseconds of each other like they all sat there and then werereleased for processing at the same time). The rest of the queries arefine. Now if I turn around and immediately run the 25 queries again,they all come back in a few seconds which is the normal amount of time.Also, if I run a query on one of views first (just one) and then runthe 25 queries they all come back in a few seconds as well.This tells me that some caching must be involved since the times are sodifferent between identical queries but I would expect that one of thequeries would cache and thus take longer but the other 10 would befast, not all block for 2 minutes. What is more puzzling is that thisbehavior didn't occur before where now the only differences are:1) 3 times more data (but that shouldn't cause a difference from 3seconds to 120 and all tables have been through the index wizard with aSQL trace file to recommend indexes)2) There is now a join between 2 tables where there wasn't before3) The tables are replicated throughout the day.I would appreciate any insight into this problem as 120 seconds is waytoo long to wait. Thanks in Advance.Chris
I have the following queries that run on a view called EntrySummary:
1)
exec sp_executesql N'SELECT COUNT (1) FROM [dbo].[EntrySummary] WHERE [EntrySummary].[SubmissionStatusID] = @SubmissionStatusID0 AND [EntrySummary].[CreatedBy] = @CreatedBy1',N'@SubmissionStatusID0 int,@CreatedBy1 nvarchar(20)',@SubmissionStatusID0=4,@CreatedBy1=N'domainaperson'
2) exec sp_executesql N'SELECT COUNT (1) FROM [dbo].[EntrySummary] WHERE [EntrySummary].[CreatedBy] = @CreatedBy1 AND [EntrySummary].[SubmissionStatusID] = @SubmissionStatusID0',N'@SubmissionStatusID0 int,@CreatedBy1 nvarchar(20)',@SubmissionStatusID0=4,@CreatedBy1=N'domainaperson'
(The only difference between the two queries being the order of the where clauses)
Both return the correct answer (4144), but Query 2 takes 10-15 seconds whereas Query 1 takes < second. If the same query is resubmitted several times, this doesn't affect the times.
(The vast majority of the records have a status of 4, but only about 3% will be created by the person).
Replacing Count(1) with count(*) makes both queries return quickly.
Is sp_executesql creating poor execution plans ? Can anyone explain this behaviour?
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
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
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 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?
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
Is there any specific place where I can find which SQL Query is more efficient?.
Is INNER JOIN is faster or Select ... Where ID in (SELECT ...) is faster?
I have two tables: 1.FLEET (The number of rows is not so much) Attributes : Company_Id (PK) Fleet_Id (PK) Fleet_Name Fleet_Description
2.USER_PRIVILEGE (The number of rows can reach up to 3 times the number of row in fleet table) Attributes : Company_Id (PK) Fleet_Id (PK) User_Id (PK) Privilege_Id(PK) Comment Category
I want to select Fleet_Id and Fleet_Name from fleet table Where the current user has privilege_id=1
I have two possible select statement :
1.Option 1
SELECT Fleet_Name, Fleet_Id FROM FLEET WHERE (Company_Id = 2) AND (Fleet_Id IN (SELECT fleet_id FROM user_privilege WHERE user_id = 11 AND company_id = 2 AND privilege_id = 1)) ORDER BY Fleet_Name
2.Option 2
SELECT F.Fleet_Name, F.Fleet_Id FROM USER_PRIVILEGE U INNER JOIN FLEET F ON U.Fleet_Id = F.Fleet_Id WHERE (F.Company_Id = 2) AND (U.Privilege_Id = 1) AND (U.User_Id = 11) ORDER BY F.Fleet_Name
Actually which one is faster. Is SQL Statement with INNER JOIN (Option 2) can be executed faster than the one with double Select Statement(Option 1)?
Hi: I have the following query, can somebody help me? SELECT s.Id, s.NameFROM Switch s INNER JOIN SwitchTelephoneRange r ON s.Id = r.IdWHERE '1526858' BETWEEN FromTelephone AND ToTelephone Where the '1526858' is a phone number. My problem is, I want to run the above query for each record in : select Telephone from PhoneDirectory So, each telephone number in the second phone, would macth the ' ' in the first query. How can I do so? Do I need a loop? a cursor? Can you help please? Thanks
Hi, I want to know query execution time as output. I want execution time only. this is for tuning purpose... Time displayed in the status bar is not helpful for me. thanks.
Hi there, I'm having a big performance problems with sql query. What i have done is changes physical layout (rearanged the columns) in one of the tables in the database. I used bcp to get the data out and then in. There are about quarter million rows in the this table. I have created the same indexes but know the same query takes a long time to execute. I have noticed that the showplan is somehow different than it used to be. This query uses the table i have changed and another one that i haven't touched. I have updated the stats to no help. Here are the show plans. this one is slow STEP 1 The type of query is INSERT The update mode is direct Worktable created for ORDER BY FROM TABLE SW_PERSON Nested iteration Index : swiPERSON10 FROM TABLE SW_CASE Nested iteration Table Scan TO TABLE Worktable 1 STEP 2 The type of query is SELECT This step involves sorting FROM TABLE Worktable 1 Using GETSORTED Table Scan
this one used to be fast STEP 1 The type of query is INSERT The update mode is direct Worktable created for ORDER BY FROM TABLE SW_CASE Nested iteration Table Scan FROM TABLE SW_PERSON Nested iteration Index : PK_SW_PERSON_1__27 TO TABLE Worktable 1 STEP 2 The type of query is SELECT This step involves sorting FROM TABLE Worktable 1 Using GETSORTED Table Scan
I think the problem is with the fact that the fist one doesn't use the PK key which is the one the links both tables. My question is how to force the query to use this index. PS. One thing i haven't done is to recreate indexes on the other table. But i don't think that would have made a differnece. Thanks
