Strange Performance Problem With SELECT COUNT(1) And Sp_executesql
Feb 22, 2008
Hello,
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?
Regards,
AndyM
View 1 Replies
ADVERTISEMENT
Jul 23, 2005
This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?
View 3 Replies
View Related
Sep 20, 2006
Hi All,
The following is cut from SQL profiler, database is SQL 2000 SP4. Query 1 takes almost zero time and 26 reads. Query 2 takes 16 millsecs and 2862 reads and the only difference is that Query2 has parameters. I have run the query's multiple times and in different order and the results are the same. My reading of the documentation says that Query 2 should be faster due to not having to recreate the execution plan. If the execution plan is a bad one and is cached how do I remove it. Is there anyway to force a recompile or am I know in stored procedure territory.
Can anyone give me an explanation as it looks like we should be changing our code to use literal's where ever possible rather than parameter substitution.
TIA
SQL:BatchCompleted exec sp_executesql N'select *
from PA_REC_RECEIPT_ALLOCATIONS
where RECEIPT_PREFIX = ''LMH''
and RECEIPT_SUFFIX = 10652
' Microsoft SQL Server Management Studio - Query 0 26 0 0 3256 51 2006-09-20 13:15:32.843
-- Query 2
SQL:BatchCompleted exec sp_executesql N'select *
from PA_REC_RECEIPT_ALLOCATIONS
where RECEIPT_PREFIX = @P1
and RECEIPT_SUFFIX = @P2
', N'@P1 varchar(3),@P2 int', 'LMH', 10652
Microsoft SQL Server Management Studio - Query 16 2862 0 13 3256 51 2006-09-20 13:15:35.830
View 5 Replies
View Related
Sep 11, 2001
I need to do some dynamic execs based on SQL strings as variables taken from a table.
I need the best performance. In testing, I'm finding that the string I am executing is taking 20 times as long to run from within an Exec or sp_executesql vs running the same string from directly within query analyzer. Of course, there's some overehead with Exec, and we're talking milliseconds (30ms in query analyzer vs 600 in an Exec), but this still seems like a lot of overhead. I assume both statements still need to be compiled and an execution plan created. Is this difference in time normal?
I ask because I'm debating about whether to execute this process (which may need to run 9000 times a second) in a stored procedure or using an executable running separately on the server. I assume an executable's performance will approximate running the same query from query analyzer.
Here's the query:
Select GroupNodeNumber, SB.* From (Select Distinct GroupNodeNumber from #QASData) RD Cross Join (SELECT * From SQL_QAS_ServiceFailure WHERE QASEventID=99077223) SB Where (GroupNodeNumber=10 and (((VerbalPOD=-1) or (Canceled=-1) or ((DeliveryRequirement) = -1) or
(DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)>240) or (InvoiceType<>0)))) or
(GroupNodeNumber=20 and ((Canceled=0) and (NOT (DeliveryRequirement) = -1) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)>240) and (VerbalPOD=0) and (InvoiceType=0))) or
(GroupNodeNumber=30 and ((Canceled=0) and (NOT (DeliveryRequirement) = -1) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)>120) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)<=240) and (VerbalPOD=0) and (InvoiceType=0))) or
(GroupNodeNumber=40 and ((Canceled=0) and (InvoiceType=0) and (VerbalPOD=0) and (NOT (DeliveryRequirement) = -1) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)<=120))) or
(GroupNodeNumber=50 and ((ShipmentStatus='POD') and (VerbalPOD=0) and (InvoiceType=0)))
View 1 Replies
View Related
Aug 12, 2006
Okay, so I came across an odd performance issue that I'm wondering if some guru can help me out with.
I have a query that uses a paging algorithm that uses a paging algorithm and a table variable, then gets a page of data based on a join to that table variable. Here's a simplified query using the algoritm:
--declare table variable... not shown for brevity
--make sure we only store the least amount of records possible
SET ROWCOUNT ( @pageNumber + 1 ) * @pageSize
--insert into table variable
INSERT INTO @TableVariable( Key )
SELECT key FROM table
WHERE whatever = @p1
--we only want one page of data
SET ROWCOUNT @pageSize
--now get the page of data from the table
SELECT key FROM table
WHERE whatever = @p1
AND [TableVar Identity Column] > @pageNumber * @pageSize
The algorithm works great for our needs, BUT, I noticed something a little odd about its behavior during performance testing.
In particular, when I run the query using Sql Server Management Studio, where I manually DECLARE all the variables it ends up needing only 156 reads to complete the job. When I call it from the app using ADO.NET, however, I noticed it needs 310 reads! Huh?
I looked for differences, and the only one I could determine was that ADO.NET passes the query and uses sp_executesql and passes the parameters vs. declaring and setting them statically before executing the query. I confirmed that this was the issue by manually running sp_execute SQL and seeing that it took roughly the same number of reads (274) to process the query.
Naturally, I don't want the time it takes to perfrom my query to double, but and frankly I don't understand why there would be a difference in performance. Can anyone help me track down what is going on and suggest to me how to fix the problem.
I assume that SQL Server Management Studio optimizes the execution path somehow, but I'm not sure how to gain the same benefit for my passed query. Can I enable something with hints? Is there something else going on that I should know about?
View 10 Replies
View Related
Jul 20, 2005
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
View 5 Replies
View Related
May 12, 2006
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
View 3 Replies
View Related
Nov 28, 2005
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 ***
View 2 Replies
View Related
Apr 21, 2008
Dear All,
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?
Please give me some hints! Thanks a lot!
Regards,
Alex
View 9 Replies
View Related
Apr 12, 2007
Hi,
I've got a strange performance issue:
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.
View 9 Replies
View Related
Jun 14, 2006
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?
-Brian
View 1 Replies
View Related
Nov 23, 2006
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.
Clive
View 8 Replies
View Related
Jun 26, 2007
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
View 8 Replies
View Related
Apr 23, 2008
Hi All,
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
);
SQLSetStmtAttr(hS, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)numrows, 0); // numrows = 2000
SQLSetStmtAttr(hS, SQL_ATTR_PARAMS_PROCESSED_PTR, &p16cRows, 0);
status = SQLExecute (hS);
SQLParamOptions(hS, 1, NULL);
SQLFreeStmt ( hS, SQL_RESET_PARAMS );
//----Sample Code -------------------------------------------------------------------------------------
Thanks,
Nick
View 5 Replies
View Related
Feb 7, 2008
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 ?
View 1 Replies
View Related
Dec 1, 2005
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
View 1 Replies
View Related
Jul 23, 2005
I need to update the columns to blanks if it has Nulls; Most of thetime we do not have Nulls and to check that in a 3.2 million table ittakes 2 minutes and 45 seconds. (To do select count(*) from <table>where <columnname> is Null) .I just cut and pasted a part from my stored procedure:EXEC ('IF (SELECT COUNT (*) FROM ' + @tblname + ' WHERE '+@columnname + ' is Null ) >0BEGINPRINT ''' + @columnName + ' has Null ''' +' UPDATE ' + @tblname +' SET ' + @columnName + ' = ''''WHERE ' + @columnName + ' is NullPRINT ''Updated ''END'Please let me know if there is a better way to do instead of doingcount (*).Thanks in advance!
View 5 Replies
View Related
Apr 21, 2008
Hello,
I have a simple count(*) query on tableA that takes 1:30 min.
The table has a pk defined and there are only 250k records.
I have already tried count(1) which takes same amount of time.
What influences count(*) performance?
View 3 Replies
View Related
Jan 26, 2007
I have a small problme with my select statement, before i explain, here are the tables i use:
forum_boardidforum_topicidboardid...
forum_answeridtopicidanswer nvarchar.....
forum_moderatorusernameboardidforum_reportusernameansweridcreated datetime
What i am trying to do is get all the reports that a moderator is allowed to see.So i tell the script what forum_moderator.username i want all the results fromThen i am trying to returnforum_report.answeridforum_report.created forum_answer.answer
I have been trying something like this:
SELECT forum_report.id, forum_report.answerid, forum_report.username, forum_answer.answer, forum_report.created FROM forum_moderators FULL OUTER JOIN forum_topics ON forum_topics.boardid = forum_moderators.boardid FULL OUTER JOIN forum_answer ON forum_answer.topicid = forum_topics.id FULL OUTER JOIN forum_report ON forum_report.answerid = forum_answer.id WHERE (forum_moderators.username = @UserName)
if forum_answer.answer = NULL then i want the program to return NULL.
When i try this i get it kinda backwards, i get:forum_report.id = nullforum_report.answerid = nullforum_report.username = nullforum_answer.answer = The actual message that i wantforum_report.created = null
What could be the problem?Patrick
View 3 Replies
View Related
Apr 14, 2007
So I'm having a bit of an off issue with a simple Select statement and I'm hoping someone can help me explain.
View 4 Replies
View Related
Feb 24, 2008
Hi guys,
I'm facing a performance issue with select count(*) table_name when performing pagination to my results. My actualy query joins 4 tables with proper PK and FK contrainsts and indices applied.
select top 100 * from table1
left outer join table2 on tid=rid
left outer join table3 on tid=aid
inner join shipment on sid=sid
where datetime>=convert(datetime,'20070810 00:00:00') and datetime<=convert(datetime,'20070920 23:59:59')
The query above takes 200 ms to 600 ms to execute.
select count(*) from table1
left outer join table2 on tid=rid
left outer join table3 on tid=aid
inner join shipment on sid=sid
where datetime>=convert(datetime,'20070810 00:00:00') and datetime<=convert(datetime,'20070920 23:59:59')
The query above takes 2000ms to 4000 ms to run.
The total records that fulfill the where clause is approximately 5000 to 6000 records. I checked the execution plan but found that the server is actually utilizing the indices with operations like index scan and index seek.
Are there any other things that i can do to improve the counting of total records?
Thanks.
View 5 Replies
View Related
Jun 30, 2004
This works:
Code:
select
a.*, b.*
FROM table1 as a
INNER JOIN table2 as b ON a.id = b.id
But this doesn't:
Code:
select
a.column1, b.column1, b.column2
FROM table1 as a
INNER JOIN table2 as b ON a.id = b.id
Returns "No. -2147217913 Data type mismatch in criteria expression". Am I just doing something stupid?
View 1 Replies
View Related
Dec 4, 2007
I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database.
The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records.
I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered).
I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these).
I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes.
I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem.
So my question is: Is it normal for such a type of read query to take 90 seconds to complete?
Is there anything I could do to speed it up.
Any other thoughts?
Thanks
View 7 Replies
View Related
Mar 26, 2007
Hi all,
I have a simple cube with only one measure : "Amount", I have three dimensions Supplier, Category and Company.
I would like to display a matrix with Categories as rows, company as columns, the amount and the number of distinct suppliers who appaers for each category/company.
I tried to create a calculated measure like this:
Count(Crossjoin({[D Supplier].[Supplier Code].members}, {[Measures].[Amount]}), EXCLUDEEMPTY)
The result is OK, but when I add this calculated Measure to my query the execution time jumps from a few seconds (<10) to a few minutes. Am I missing something ?
Anyone have an idea to do this in a better way ?
View 8 Replies
View Related
Mar 18, 2008
Hi All,
I've got the following situation.
I've got a report that has 3 multiselect parameter selection dropdowns in my report.
Dropdown 3 is depending on the selected values of dropdown 2.
Dropdown 2 is depending on the selected values of dropdown 1.
For example:
In dropdown 1 the values A1 and A2 are available
when I select a value A 1 in dropdown 1 values A1_B1 and A1_B2 are available in dropdown 2
when I select A1 and A2 in dropdown1 values A1_B1, A1_B2, A2_B1 and A2_B2 are available in dropdown 2
when i select A1_B1 in dropdown 2 values A1_B1_C1,A1_B1_C2 are available in dropdown 3
when i select A1_B1 AND A1_B2 IN dropdown2 values A1_B1_C1,A1_B1_C2, A1_B2_C1,A1_B2_C2 are available in dropdown 3
dropdown 1 | dropdown 2 | dropdown 3
A1 | A1_B1 | A1_B1_C1
| | A1_B1_C2
| A1_B2 | A1_B2_C1
| | A1_B2_C2
in the "picture" above I describe the selection i make in the report
so
in dropdown 1 I select A1
in dropdown 2 I select A1_B1,A1_B2
in dropdown 3 I select A1_B1_C1,A1_B1_C2,A1_B2_C1, A1_B2_C2
then the strang happens.
When I look in profiler to see what happens, is that for dropdown 2 the second value for the parameter of dropdown 2 is
and for dropdown 3 all except the first one is missing.
The values from the dropdownboxes are passed through to a subreport. When the selected values from dropdown boxes are passed though directly to the datasets this problem doesn't occur.
Is this normal behaviour? Or do I miss something.
Thanks for your help,
Eric
View 1 Replies
View Related
Jun 21, 2006
I have created two select clauses for counting weekdays. Is there a way to combine the two select together? I would like 1 table with two columns:
Jobs Complete Jobs completed within 5 days
10 5
-------------------------------------------------------------------------------------------------
SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @SDCompleted) AND (SDCompleted < @SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)
---------------------------------------------------------------------------------------
Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @SDCompleted) AND (SDCompleted < @SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')
View 9 Replies
View Related
Aug 23, 2013
I am using three tables in this query, one is events_detail, one is events_summary, the third if gifts. The original select statement counted the number of ids (event_details.id_number) that appear per event_name (event_summary.event_name).
Now, I would like to add in another column that counts the number of IDs that gave a gift who attended an event that were also listed in the event_ details table. So far I have come up with the following. My main issue is linking the subquery properly back to the main query. how to count in the sub-query and have the result placed within the groups results in the main query.
SELECT es.event_name, es.event_id, COUNT(ed.id_number) Number_Attendees,
(
SELECT COUNT(gifts.donor_id) AS Count2
FROM gifts
WHERE gifts.donor_id = ed.id_number
) subquery2
[code]....
View 1 Replies
View Related
Oct 25, 2007
Hello, I would like to count the number of items in a table. I used the following code:1 Dim Comments As Integer
2 Dim cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString())
3 Dim SqlCommand As New SqlCommand("SELECT COUNT(CommentID) FROM Comments WHERE ThemeID = '3', cnn")
4
5 cnn.Open()
6 Comments = SqlCommand.ExecuteScalar()
7 cnn.Close()
But this only gives me the error message "ExecuteScalar: Connection property has not been initialized." Can anyone help me with this? Thanks
View 1 Replies
View Related
Nov 18, 2007
I need to select total number of rows from my data base table....here is what ive been trying....I know it wrong...but maybe someone can fix it. Thank you very much.
Function DBConnection(ByVal strUserName As String, ByVal strPassword As String) As BooleanDim MyConn As New Data.SqlClient.SqlConnection(ConnectionString)
Dim cmd As New Data.SqlClient.SqlCommand("Select count * from ClassifiedAds", MyConn)Dim dr As Data.SqlClient.SqlDataReader
'cmd.Parameters.Add(New Data.SqlClient.SqlParameter("@UserName", textbox_username.Text))
' cmd.Parameters.Add(New Data.SqlClient.SqlParameter("@Password", textbox_password.Text))
cmd.Connection.Open()
dr = cmd.ExecuteReader()
dr.Read()
If dr.HasRows Then
Label_totalclassifieds.Text = dr.Read
Return True
Else
dr.Close()
cmd.Connection.Close()
Return False
End If
End Function
View 8 Replies
View Related
Mar 14, 2008
I have sql statement like "select count(*) from table where id = 1", and I want to assign the result to label.text. How do I do that? Thanks.
View 7 Replies
View Related
Aug 26, 2004
Hello,
I'm new to SQL.
For a statistic application, I wish know the subtotal of lines pro region (Mitte, ost, west, ost, etc).
How can I do that?
A lot of thx for your help and time,
Regards, Dominique
Code:
SELECT
distinct case
when ANZSUCHEN.KANTON = '' then 'nicht_zugeteilt'
when ANZSUCHEN.KANTON = '----------------------------------' then 'nicht_zugeteilt'
when ANZSUCHEN.KANTON = 'AG' then 'mitte'
when ANZSUCHEN.KANTON = 'AI' then 'ost'
when ANZSUCHEN.KANTON = 'AR' then 'ost'
when ANZSUCHEN.KANTON = 'BE' then 'bern'
when ANZSUCHEN.KANTON = 'BL' then 'mitte'
when ANZSUCHEN.KANTON = 'BS' then 'mitte'
when ANZSUCHEN.KANTON = 'FR' then 'west'
when ANZSUCHEN.KANTON = 'GE' then 'west'
when ANZSUCHEN.KANTON = 'GL' then 'ost'
when ANZSUCHEN.KANTON = 'GR' then 'ost'
when ANZSUCHEN.KANTON = 'JU' then 'west'
when ANZSUCHEN.KANTON = 'LU' then 'mitte'
when ANZSUCHEN.KANTON = 'NE' then 'west'
when ANZSUCHEN.KANTON = 'NW' then 'mitte'
when ANZSUCHEN.KANTON = 'OW' then 'mitte'
when ANZSUCHEN.KANTON = 'SG' then 'ost'
when ANZSUCHEN.KANTON = 'SH' then 'ost'
when ANZSUCHEN.KANTON = 'SO' then 'mitte'
when ANZSUCHEN.KANTON = 'SZ' then 'mitte'
when ANZSUCHEN.KANTON = 'TG' then 'ost'
when ANZSUCHEN.KANTON = 'TI' then 'west'
when ANZSUCHEN.KANTON = 'UR' then 'mitte'
when ANZSUCHEN.KANTON = 'VD' then 'west'
when ANZSUCHEN.KANTON = 'VS' then 'west'
when ANZSUCHEN.KANTON = 'ZG' then 'mitte'
when ANZSUCHEN.KANTON = 'ZH' then 'ost'
end as region,
(SELECT count(*) FROM ANZSUCHEN WHERE ANZSUCHEN.KANTON = 'FR')
FROM
ANZSUCHEN
GROUP BY
ANZSUCHEN.KANTON
ORDER BY
region
Results:
Code:
region (No colomn name)
bern34
mitte34
nicht_zugeteilt34
ost34
west34
View 3 Replies
View Related
May 3, 2007
I have the following code in VB.Net trying to count # of records in the datatable. Upon execution of the code, it returns only 1 record. I know for fact that there are more than 1 record in the datatable (there are 230 records in the table).
Can somone please tell me where is my problem ?
Public Function Get_Record_Count(ByVal Table_Name As String, ByVal Criteria As String) As Long
Dim tbl As DataTable
strSQL = "SELECT count(*) FROM [" & Table_Name & "]"
If Len(Criteria) > 0 Then
strSQL &= " WHERE " & Criteria
End If
ADO_Adapter = New OleDb.OleDbDataAdapter()
tbl = New DataTable(Table_Name)
ADO_Adapter.SelectCommand = New OleDbCommand(strSQL, ADO_Conn)
Dim Builder As OleDbCommandBuilder = New OleDbCommandBuilder(ADO_Adapter)
ADO_Adapter.Fill(tbl)
'Return # of records found in table
Get_Record_Count = tbl.Rows.Count.ToString
tbl.Dispose()
End Function
View 1 Replies
View Related
Jan 9, 2008
Hallo!
I have a table student(teacherID, studentID, studentName...) that contains data for a certain student and a table teacher(teacherID, teacherName...) that contains data for the teacher. Student can be imagine as follow:
tID | sID ... other fields
----------
1 1
1 2
1 3
1 4
2 5
2 6
2 7
3 8
For example, teacher of tID=1 teachs to 4 students.
I'd like to select all the fields from teacher where the teacher has more than x students.
Is it possible? How can I do?
Thank you!
View 16 Replies
View Related
