I have 2 different queries which produce same result. I want to know which querry is better and why?
The query is used to display the employee details who is handling the maximum number of project.
Queries are the following
Query A
Code Snippet
SELECT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
COUNT(LUP_EmpProject.Empid) AS Number_Of_Projects
FROM LUP_EmpProject
INNER JOIN EmployeeDetails
ON LUP_EmpProject.Empid=EmployeeDetails.Empid
GROUP BY EmployeeDetails.FirstName+' '+EmployeeDetails.LastName,
LUP_EmpProject.Empid
HAVING COUNT(LUP_EmpProject.Empid)>0
AND COUNT(LUP_EmpProject.Empid)=(SELECT
MAX(Number_Of_Projects)
FROM (SELECT COUNT(LUP_EmpProject.Empid) Number_Of_Projects
FROM LUP_EmpProject
GROUP BY LUP_EmpProject.Empid)AS sub)
Query B
Code Snippet
SELECT LUP_EmpProject.EmpID,
EmployeeDetails.FirstName + ' ' + EmployeeDetails.LastName AS EmpName,
COUNT(*) AS NumberOfProjects
FROM LUP_EmpProject
INNER JOIN EmployeeDetails
ON LUP_EmpProject.EmpID = EmployeeDetails.EmpID
GROUP BY LUP_EmpProject.EmpID,
EmployeeDetails.FirstName + ' ' + EmployeeDetails.LastName
HAVING COUNT(*)=(SELECT
MAX(Number_Of_Projects)
FROM (SELECT COUNT(LUP_EmpProject.Empid) Number_Of_Projects
FROM LUP_EmpProject
GROUP BY LUP_EmpProject.Empid)AS sub
I need to build TSQL query to return the Last unit Cost from my table of movement of goods SL (on CTE) but the MAX(Datalc) must be Less or Equal to my HeaderInvoice.
This is my script:
With MaxDates as ( SELECT ref, MAX(epcpond)[Unitcostprice], MAX(datalc) MaxDate FROM sl
[code]....
the problem I have right now is that the Unitcostprice of my table of goods movements has a top date greather than the date of my bill.
Example:
invoice date : 29.01.2015 unitcost on invoice line = 13,599722 Maxdate (CTE) : 19.03.2015 unitCost from my table of movement of goods = 14,075
That ´s not correct because the MAxdates > invoice date and the unitCost of 14,075 is the cost on 19.03.2015 and not just before my invoice date.
I right in thinking that if the estimated subtree cost is higher than the cost threshold for parallelism then it will use a parallel plan? If so, I've read the cost threshold is measured in minutes but is the subtree cost measured in something else, the mysterious cost number? And if so, how are the two compared?
I have a query i have been optimizing. Now runs in about 15 minutes but was wondering if there is any way tr educe the SORT cost.
Currently the high costs left are the Table insert which is 58% and the Sort cost of 36%
The inner query below is around 400million rows and aggregates to around 15,000,000 rows)
SELECT@1 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -1, GETDATE() -1) as DATE) SELECT@2 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -2, GETDATE() -1) as DATE) SELECT@3 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -3, GETDATE() -1) as DATE) SELECT@4 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -4, GETDATE() -1) as DATE) SELECT@5 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -5, GETDATE() -1) as DATE)
I have enabled the query governor on our SQL2000 SP2 server with athreshold of 3600. Now, some of the maintenance jobs fail due to thelimit being to low (e.g. one of the user databases integrity checkfails nightly).I have tried to put the command 'SET QUERY_GOVERNOR_COST_LIMIT 0' justbefore the line in the step which reads 'EXECUTEmaster.dbo.xp_sqlmaint N'-Plan etc'but it has no effect.Does anyone know how to get around this situation without usingsp_configure to change the query governor settings at a systemwidelevel?GC.
I am using a stored procedure that is behaving badly - the subtree cost is about 2000 and it takes between 3-4 seconds to run, and sometimes it takes over a minute to run. I have made some optimizations that cause the stored procedure to run in generally under 1 second (at most under 2 seconds), but the subtree cost of it jumps to 4000!! All of this while the server was experiencing similar load (the tests were done within minutes of each other).
I know that the subtree cost is a way to gauge the performance of a query against other queries, but I have typically seen the cost go in the same direction as the execution time (they both go up or the both go down).
How does SQL Server determine the cost (I know that is based on statistics, but I was wondering if anyone had more details)? Is it more important to have a lower subtree cost, or a lower execution time? Am I going to get into trouble later with this high subtree cost?
I want to turn on the query governor cost limit option for 20 minutes so that queries do not run longer than 20 minutes but I could not find any info as if this option would also not allow database backup job or other maintenance jobs to run more than 20 minutes. We have backups (Via RedGate) run over 3.5 hours and others like rebuild indexes and integrity checks even more than 3.5 hours....
I have two queries yielding the same result that I wanted to compare for performance. I did enter both queries in one Mangement Studio query window and execute them as one batch with the actual query plan included.Query 1 took 8.2 seconds to complete and the query plan said that the cost was 21% of the batchQuery 2 took 2.3 seconds to complete and the query plan said that the cost was 79% of the batch.The queries were run on my local development machine. I was the only user. No other programs were running at the time of this test. The results are repeatable.I understand that the query with the lowest cost is not necessarily the fastest query. On the other hand, the difference is quite big. The query that has approx. 80% of the cost takes 20% of the time and the other way around. I have two questions:
Is such a discrepancy normal?Can conclusions be drawn from the cost distribution? For instance, does the query that takes 8.2 seconds but only costs 21% scale better?
I wrote some code to insert some data into a table, if the record already exists overwrite, etc. I've never done something like this so I am going to bang my head against that for a while. But when I did an execution plan I got
Index Seek Cost : 105 %
How do I get more than 100% and what does that mean?
OK, this should be an easy one but my brain isn't quite working right now.
I have a table, we'll call Table1 like so:
ProductID Supplier Cost 12345 A 14.50 12345 B 13.49 12345 C 12.00 43222 A 15.00 43222 B 15.21 43222 C 13.99 12312 B 14.00 15421 A 21.99 15421 C 20.00
And I want to Get the name of the Supplier with the Lowest cost, I know I can go like:
SELECT ProductID, MIN(Cost) FROM Table1 GROUP BY ProductID
and get the lowest cost, but what would be the most effiecent way to get all three fields returned by the query? I need the ProductID, Supplier and Cost.
I am querying a database that has hours worked listed by pay rate. I want to return sum of hours per pay rates I have been given but hours are stored in multiple columns. So If I run the below query for one worker it returns.
Select Sum(Hours_1) As Hours,Bill_rate_1,Sum(Hours_2) as Hours_2,Bill_rate_2--,Hours_3,Bill_rate_3 From Valid_Timesheets Where (Department = '938' and tax_period = 3 and tax_year = 2015 AND PERSONNEL_REF = '991A001198') and ((Bill_Rate_1 = £10.26 or Bill_Rate_1 = £9.67 or Bill_Rate_1 = £8.27 or
I've converted most SQL Server databases to unicode since the AS 400 stores data in unicode (UTF-16). So far the only difference I see is that running queries to text in Unicode takes a lot longer, and UTF-16 files are 4x as large as Cp1252 files.
Any way to get the file sizes / query times of old with Unicode?
After lots of testing I have found that re-using dialogs improves performance significantly, however I cannot fathom out what is the cost of re-using them. Performance does not come for free hene why I feel there is a cost. Can anyone shed any light on the cost of re-using dialogs? Also why should you re-cycle them? What benefits does recycling dialogs have?
has anybody investigated the cost of turning on AUDITING for the SQL servers? I am talking about enabling the entire C2 Security Audit mode. How much impact the auditing has on the database performance?
Is auditing for SQL 2005 any better, meaning less impact on performance?
Any sample, test, and/or numbers to support the arguments?
Please share any findings that you have or know. Thanks
I have been told that many ETL tools used to cost hundred of thousands of dollars. Is this true or were they referring to the ETL and Analysis tools, together? Can anyone provide some actual product names and estimated "costs" that hits these marks?
im try to get the subtree cost (sql server 2000) back from a dynimic sql query that i build on the fly.. the sql query is:
SET SHOWPLAN_ALL ON GOselect * from mp_Contacts Where FirstName = 'Jason' --my queryGOSET SHOWPLAN_ALL OFF GOGO
the c# code is: <code> Decimal HoldSubTree; // Make Connection SqlConnection FindSubTree = new SqlConnection(Utilities.GetCRMEnvironment()); //Sql Connection System.Text.StringBuilder SHOWPLANALLON = new System.Text.StringBuilder("SET SHOWPLAN_ALL ON"); System.Text.StringBuilder SHOWPLANALLOFF = new System.Text.StringBuilder("SET SHOWPLAN_ALL OFF"); System.Text.StringBuilder SubTreeSql = new System.Text.StringBuilder(MySqlString.ToString() + " "); // Grab the data and fill the dataset SqlCommand SubTreemyCommand = new SqlCommand(SHOWPLANALLON.ToString(), FindSubTree); SubTreemyCommand.CommandTimeout = 90; SubTreemyCommand = new SqlCommand(SubTreeSql.ToString(), FindSubTree); SubTreemyCommand.CommandTimeout = 90; SqlDataAdapter SubTreemyDA = new SqlDataAdapter(); SubTreemyDA.SelectCommand = SubTreemyCommand; DataSet SubTreemyDS = new DataSet(); SubTreemyCommand = new SqlCommand(SHOWPLANALLOFF.ToString(), FindSubTree); SubTreemyCommand.CommandTimeout = 90; try { SubTreemyDA.Fill(SubTreemyDS); </code> and the result: it bring back the rows of data from the dynimic query instead of the subtree cost.. how could i send 3 statements to the sql database but all in the same transaction.. for example: first the server would switch modes: "SET SHOWPLAN_ALL ON" then i would send it my query: "select * from mp_Contacts Where FirstName = 'Jason' " and then switch the server back over: "SET SHOWPLAN_ALL OFF" Is this possible?? ideas??
We use SQL Server 2005 and we use lots of stored procedures. Each time a stored procedure run we log the name, execution time and username so we can identify trends and long-running procedures. I would like to add the actual CPU and IO cost of execution to this log. Is there a way to get it? This type of information is viewable in SQL Server's performance tools - so there must be some way to get it. Can someone point me in the right direction please?
We are in need of a JDBC driver (version 3) to support some autonumbering in SQLServer. We've found a couple, very expensive stuff (JTurbo & DataDirect), these drivers can be more expensive than a SQLServer license. Any other ideas/sources?
Now if the month and year of the start and finish date is same, the cost remains same... but if the month of the two dates are different, i have to distribute the cost between the two months by calculating the cost for the number of days for both of the months.. but i am not able to figure out how? i am using sql 2005 .. my table has got about 1 million rows... pls help.....dll is as followsGO CREATE TABLE [dbo].[DSS]( [Service Start] [datetime] NULL, [Service End] [datetime] NULL, [FMIS Code] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL, [Client NHI] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL, [No of Units] [float] NULL,
I am collecting cost and other information for my manager who is willing to set up an "in house" web dev dept. Will SQL server express always be free? What does it lack from the regular version of SQL server? How would it compare with other DB's available for web use. This isn't a retooled Access thing, is it?
i am trying to find it, the only thing the MS site shows is enterprise licensing. is the developer edition still going to be like $100 or whatever it was?
I've a table similar to the one below, with a SKU, Category and Cost, and need using a simple select command, split the cost in two columns one for each category (1,2), I used a self-join, and it works, but it doesn't show values not equal in both categories
Declare @Tmp_SKUCatValue Table( SKU char(7) ,Cetegory Int ,Unit_cost Decimal ); INSERT INTO @Tmp_SKUCatValue (SKU, Cetegory,Unit_cost) Values ('sku-001',1,120)
I need calculating a rolling 3 month average cost from the two dataset below. Which is the 3 month Average of Dataset1 / Dataset 2.
Dataset 1:
SELECT(factAdmissions.ContractCode + '-' +factAdmissions.BenefitPlanCode) AS [Contract Code], factAdmissions.AdmitCCYYMM, ISNULL(sum(AmountPaid),0)As [Amount Paid] FROM factAdmissions
[Code] ....
Dataset2:
Select
(factMembership.ContractCode+'-'+ factMembership.BenefitPlanCode) As Product, EffectiveCCYYMM, ISNULL(count(Distinct MemberId),0) As MemberCount From factMembership Where EffectiveCCYYMM >= '200701'
Edited by SomeNewKid. Please post code between <code> and </code> tags.
// Pls copy the following HTML after that you generate the HTML page. Then you will know what I mean. I need to form "New Table" from Job Revence and Job Cost using SQL statement? thx
