Dear Experts,I have a fairly simple query in which adding a where clause slowsthings down by at least a factor of 100. The following is the slowversion of the query-------------------------SELECT * FROM( Select x.event_date From x FULL OUTER JOIN yON x.event_date = y.event_date) innerQWHERE ( innerQ.event_date >= {ts '1980-01-01 00:00:00'} )------------------------Removing the where clause makes the query run quickly. This seemsextremely strange because it seems like SQL Server should simply beable to take the results of innerQ and discard anything with a datethat doesn't match. If I instead split the query into two pieces whereI create a temp table and put innerQ into that and then do the select *WHERE (...) from the temp table things work fine.Any thoughts on what SQL Server might be doing to make things slow andhow I can fix it?Thanks,-Emin
Table Master Table Dimension ID Code Price ID Name 1 A44333 5000 1 "Scanner" 2 D442 3000 2 "Notebook" 3 D6644 4000 3 "Banana"
I join both tables on ID and search one time for ID and another time for Name. Looks like
(a) SELECT AVG(Price) From Master JOIN Dimension ON Master.id = Dimension.id WHERE master.id=1 AND Code like 'A44' (b) SELECT AVG(Price) From Master JOIN Dimension ON Master.id = Dimension.id WHERE Name = 'Scanner' AND Code like 'A44'
Why does query (b) take longer than query (a)? Dimension has 12 Rows and Master has about 24M Rows.
For index I did Create Index IX_Master_ID on Master(ID) Create Index IX_Master_Code on Master(Code) Create Index IX_Dimension_ID on Dimension(ID) Create Index IX_Dimension_Name on Dimension(Name)
I noticed, that when i leave the Code like 'A44' clause, query (a) and (b) do take same time. I'm really confused. Can someone please help me out?
I am using an aggregate with the OVER clause.Running the script is fast less than 1 second but when I say insert into a temp table the execution plan is very different at it take 8 seconds.I have attached the execution plans. Also the Statistics IO, Time messages. I am using SQL Server 2014 with backward compatibility to 2008 R2.
if (select OBJECT_ID('tempdb..#MM')) is not null drop table #MM CREATE TABLE #MM ([MyTableID] [int], [ParticipantID] [int], [ConferenceID] [nvarchar](50), [Points] [money], [DateCreated] [datetime], [StartPoints] [money], [EndPoints] [money], [LowPoints] [money], [HighPoints] [money]) insert into #MM ([MyTableID], [ParticipantID], [ConferenceID], [Points], [DateCreated], [StartPoints], [EndPoints], [LowPoints], [HighPoints]) selectmm.MyTableID, mm.ParticipantID, mm.ConferenceID, mm.Points, mm.DateCreated,
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1
ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id
GROUP BY dbo.Track_ID.TrackID
)
) Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.
I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?
UPDATE dbo.Track_ID
SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed
1) Rows ordered using textual id rather than numeric id
Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v order by v.id
Result set is ordered as: 1, 11, 2 I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16 A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v cross join ( select 1 id union select 2 id union select 11 id ) u order by v.id ,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
what is differance between conditions put in ON clause and in WHERE clause in JOINS????
see conditions that shown in brown color
select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate, d1.Audit, d1.ExpirationDate, d1.Indicator from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2 on d1.SummaryID=d2.SummaryID AND d1.ListType = d2.ListType AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null) AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null) whered1.ImplicitID >= d2.ImplicitID AND (d1.SourceID<>d2.SourceID OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL) OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL) )
select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate, d1.Audit, d1.ExpirationDate, d1.Indicator from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2 on d1.SummaryID=d2.SummaryID AND d1.ImplicitID = d1.ImplicitIDAND d1.ListType = d2.ListType AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null) AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null) whered1.ImplicitID >= d2.ImplicitID AND (d1.SourceID<>d2.SourceID OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL) OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL) )
another thing...
if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove d1.ImplicitID >= d2.ImplicitID from WHERE clause????
Hi everyone, I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
I am writing a data access web page, but I find that the excution speed is too slow. My data base is just a data table which have five columns: id, code, quantity, price and Date. The data base has about 45000 rows. When I use OSQL or Query function, speed is just fine.
Here is the main code which I think cause the speed slow:
string conn = ConfigurationSettings.AppSettings["connectionstring"]; SqlDataAdapter adapter_2 = new SqlDataAdapter("select * from table",conn); DataSet ds = new DataSet(); adapter_2.Fill (ds,"table"); DataTable YahooOrders = ds.Tables["YahooOrders"]; DataRow[] product = new DataRow[20000];
.......
foreach (string s in split) // actually the split here has only one string in it { product = table.Select ("code like '"+s+"%' and Date >='"+minDate+"' and Date <='"+table.Select("Date = Max(Date)")[0][1].ToString()+"'"); foreach(DataRow myRow in product) { int count = Convert.ToInt32(myRow[2]); itemQuantity = count + itemQuantity; revenue = Convert.ToDouble(myRow[3]) * count + revenue;
// get product code, ignore repeated code int myIndex=code.BinarySearch( myRow[1] ); if ( myIndex < 0 ) code.Add(myRow[1]); } orderQuantity = product.Length + orderQuantity; } The first foreach actually excutes just one time, so it won' t cause any speed problem. The second foreach' s job is to sum each column of specified rows which is product here. So, any ideas about this? Thanks!
Actually from the application the developers are using count(column) to know the no. of rows resulted by a statement which joins many tables but its taking lot of time.
Is there an easy way to get the count of records(result set) of the output.
I cant use sysindexes b'z i need the count of the output genereted by the SQl Statement which joins many tables and retrieves many rows.
Got a problem (duh!). My MSSQL Server lags. Now, mind, it doesn't lag all the time. And it seems to be independent of the # of users trying to access the server. And it random clears itself up. And the problem doesn't present itself in SQL MGR, just on the web app we're running on it.
Setup: SQL Server 2k running on 2k3 w/ IIS & backup exec. All SQL data files are on a raid5 SCSI U160.
App: Intranet App developed by us for us. ASP.NET & VB.NET.
Symptoms: When queried server takes a LONG time to respond. So long infact it has become counter productive. When taking a look at the server, the CPU usage hovers between 50-75% and spikes up to 90% every now and then just for kicks. The memory usage is 2.35gb out of 4gb. To fix this we have to kill and restart all the SQL services.
Any thoughts on what to look at? There're indexes on the required FKs and the heavily queried columns. We're at a loss here.
A vendor's application is performing slow. Vendor tested it in QA and it's slow. End-users run it in PRD and it's slow. The application calls SP1, and SP1 calls SP2. Inside SP1 has a cursor. I believe as the db gets larger. The application is going to be even slower. What can I suggest to the vendor in order to fix it? Tell them to re-write the application code? Eliminate cursor?
I have 4000 record in my table employee. it takes 13 sec to get data. It this normal ? What is wrong ?
Thanks
Code Snippet
CREATE FUNCTION [dbo].[VrniStrukturo] (@id_sod int) RETURNS TABLE AS RETURN ( WITH tree(id, parent_id, naziv, nivo) AS ( -- Base case SELECT id, parent_id, naziv, 1 as nivo FROM employee WHERE id = @id_sod
UNION ALL
-- Recursive step SELECT e.id, e.parent_id, e.naziv, eh.nivo + 1 AS nivo FROM employee e INNER JOIN tree eh ON e.parent_id = eh.id )
--SELECT * SELECT id FROM tree --ORDER BY nivo, priimek, ime );
There re certian times when I want to execute a sql request (select for example) then It gets too long before I get an answer. (that happens only some times exceptionnaly). What does that mean, is it that somebody is using heavily the DB or may be using Entreprise manager or what exactly and how can I know who is responsible for taking all SQL server resources at that specefic time. What command or what tool can I use pls for this purpose. Thanks for your help.
Hello,Were using the data transfermation service to copy in an Ingres II 2.5database to an SQL Server 2000 database. Small databases don't present anissue, but when pulling one across that's about 20GB its been taking between12 and 24 hours. Both systems are relitively quick boxes and neither of themare tapped out on processor, disk I/O or network resources.I do have the "Boost SQL Server priority on Windows" checked under it'sproperties and all the processors are checked to be used.Does anyone know if there's a way to tweak SQL Server or Ingres to handlethis a little quicker? Or even an idea where the bottleneck could be may behelpful.Thanks,John.
I've got a performance question about a clr tvf that I have created. When I query the function it takes about 30 seconds for it to execute as apposed to < 6 seconds when I execute the same code in a console app (the 6 seconds includes outputting the returned data to the console, without writing the output to console it executes in about 1 second). Both the function and the app are iterating (>40,000) and returning ( >10,000) the same number of rows. I've noticed the following when viewing the executions in the PerfMon:
* the sqlclr tfv kicks the % Processor Time up to 30 for 30 seconds, the console app has % Processor Time at 9 for about 2 seconds * .NET CLR Memory - Allocated Bytes/sec spikes anywhere from 1 to 3 times during the sqlclr query at about 44MB/sec. It barely registers if at all when the console app runs. * In either case, % Time in GC is at zero.
I'm assuming that there are some configurations I'm ignorant of that can help me tune the execution. I can't imagine that it takes SqlServer that long just to iterate through the records.
I have recently decided to make the change from Microsoft access to SQL Server believing that it's a bigger faster beast with better parameterized queries and triggers and all that. BUT.I have some client data that I imported from their original paradox files.The invoice lineitem file contains over 1 milliion records.When I open this table in access and click show last record, the record is displayed in about 1 or 2 seconds.I used the upsize to SQL Server tool in Access to shift my data into SQL Server.When I use the Express Mangagement tool to open the same table and say show me the last record, it takes 17 minutes.I admit that most numeric data types have been translated to floats, so that's probably not good.But I cant alter them from floats to numeric or decimals using the table design tool.Do the conversion anomalies make up the whole reason why SQL Server seems so incredibly SLOW! ?????????
I am running the following BCP to extract a table with 156641604 rows.
bcp TestDB..data out test3.bcp -T -b1000000 -a32000
When running this i notice that the disk read bytessec counter in performance monitor on the drive that has the database devices is only reading 30mbsec. I am writing the bcp file to a different drive. Both drives are far more capable of achieving much higher IO. Is this a limitation with BCP or are there futher switches available that would speed this process up. Also the drives are both local so the bottle neck is not network. Any ideas?
Has anyone else noticed delays with SQL Express? I'm not really talking about delays on the queries but just delays in general response. For example: everything is running great, then for about 2 minutes I get connection timeouts etc can't even open stuff in the management studio without getting timeouts ... then as strangely as it started everything goes back to normal and requests are served again. The server has nothing on except 1 website, its Win 2003 Server. 512MB Ram on a PIV. The memory usage is low and during the "lockups" the machine isn't showing any processor usage and SQL mem usage is around 40Megs. I am not using User Instances either. Nothing in the event logs. What is odd, is its happening on 3 of my machines ..... all with different sites, the only thing in common between them is SQL. thanks, -c
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?
I need urgent help, about someting: i've developed and deploy an aspnet web site (data works with sqlserver), but after a few minutes working with some users, the permormance slows and stop the site.
Dear All,Finally I completed my project. Thanks all you helped me to do it.Now I have the biggest problem. In my application the Data grid is filled with Data from SQL server table which has large number of records. When I run my queries in SS Management Studio it runs very fast. To fill data to datagrid it takes lots of time. How can I reduce this time. How can I increase the performance of my Application.Thanks,Janaka
Hello, We have Sql Server installed on the Windows 2000 machine. There are 2 databases that the employees access on it. When the machine is just started, there are no problems but after some time the connections get really really slow and then eventually become impossible to connect. We did not have this problem before until we had a computer crash and then Sql server was installed on a new machine. The problems started here. We had 64mb of ram and we put in another 64mb thinking this would solve it but it did not. On the task manager the cpu percentage stays constant at 100% and the virtual memory increases and increases very slowly until it has reached it's maximum (which is 600mb). Any information would be greatly appreciated. Thanks very much, Kostas
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!
Hi, Some of my queries are running too slow.It's taking as long as 30secs .Earlier the same query was taking less than 5 secs. I understand the db has grown BUT I do not know to look at this query where should i start from and what should I look into. It is on production server. the db size is 15GB and unallocated is 9GB. log space used is 4%. TIA.
