We are doing a lot of selects against a table that has one large field
in it.
If we do a select against all the fields except for description, the
query comes back relatively quickly. If we add that last field (768
chars) to the query, our query takes 10x longer (5 seconds vs 56
seconds.)
When we run the one without the description column, we can watch
perfmon and see a very quick spike to physical disk. If we add in the
description field we can see that the server becomes I/O bound - the
disk sits at 100% until the query is complete.
We have tweaked the min query memory setting for the server but it
seems to have had no effect no matter how high we set it. Is there
some point at which MSSQL decides it cannot perform the transaction in
memory? What would I increase to cure this problem?
I've two tables that I've made from some query subsets. Each table has a varchar field with notes/memos and I want to concatenate the fields into one long field.
The problem I'm running into is that when I run the query to check the concatenation, the field is truncated maybe 256 chars in.
I tried converting and casting the field as nvarchar 4000, and I've also done the same for the fields in the two tables, but that doesn't seem to help.
I can query for the fields from each table and none of them are truncated by themselves. It only happens after I concatenate them.
I've created a new table and inserted the results into it, but the field in it is also truncated.
As we all know, there is a 8060 bytes size limit on SQL Server rows. Ihave a table which requires a number of text fields (5 or 6). Each ofthese text fields should support a max of 4000 characters. We currentlystore the data in varchar columns, which worked fine untill ourappetite for text fields increased to the current requirement of 5, 6fields of 4000 characters size. I am given to review a design, whichesentially suggests moving the text columns to a separate TextFieldstable. The TextFields table will have two columns - a unique referenceand a VARCHAR (4000) column, thus allowing us to crossreference withthe original record. My first impresion is that I'd rather use the SQLServer 'text' DB type instead, which would allow me the samefunctionality with much less effort and possibly better performance.Can anyone advise on advantages and disadvantages of the two optionsand what the best practice in this case would be.Any advise will be well appreciated.Tzanko
We have a proc that adds some fields to a few tables of ours and normally there are no issues. For one of our client databases this process is taking anywhere from 5-10 minutes to add the fields. This causes an issue where the app will timeout waiting. After plugging around and looking at the proc and trying different items i found it to only be for this one database and ONLY when there is data in the table. If i truncate the table and run the same procedure everything is fine. Tables all have same index on 4 columns and the columns being added are not indexed because of the stupid hoops we have to jump thru to pre-pivot data for our reporting package.
I have a 2GHZ cpu with 1GB of RAM. I occassionally see very slow (long) queries against a local SQL Server 2005 Express (SP2) database. The issue occurs against different SQL Queries, but all queries are rather basic select statements Perfmon shows that the SQL Server counter for the "MEMORY GRANT QUEUE WAIT Avg MS" gets extremely high (25000+ ms). Perfmon also also shows that PAGING is not occuring, and the system is not under unsual stress. The problem is not reproducible with MSDE.
Has anyone seen this issue, or have any recommendations for a next course of action?
Recently here at work we have been having a lot of our web applications time out after a couple minutes. Really doing just basic things like counting the number of records in the database, etc.
I went on to the machine itself (win2k) and looked at what MSSQL server was doing in the process list... and it was taking up a mind boggling 851 megabytes of memory. Out of a gig of RAM on that machine there was only 13mb available.
We restarted it last night and when I checked it today it has already grown back up to 460mb.
Is this normal?? I've never heard of applications taking up this much ram... especially since its not like anyone, or anything, is pulling down 850mb every second from the database.
Is there some kind of known memory leak in MSSQL server (8.00.760 SP3)? Or is there some other reason our web apps are now timing out, when previously they never did ...
On one of our intranet SQL servers running under Windows 2000 SP 3, MSsql 2000 SP4 seems to gradually "eat" away all available memory (with no obvious reason for it) until a certain limit is reached, forcing the server to slow down substantially since the OS has to SWAP continuously.
I would appreciate any suggestions at this point on how to tackle this problem :)
Hi, I have one application in two different companies with MSSQL2000 running on Windows2000 Server and Windows2003 Server. It seems that memory needed for MSSQL2000 as displayed in task manager is increased every day. Does anybody knows anything regarding memory leaks in MSSQL2000?
How can I find what version of service pack have I installed in a MSSQL2000 server machine?
So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.
From Books Online: • This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician. • The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.
I've got a stored proc used for order generation which runs long sometimes when called from within our app. A normal run will complete within 20s, a long run will get terminated by the app at the 6 minute mark.
When it runs long once, repeated attempts will also do so until I execute the same query the app did, but from within Query Analyzer. At which time the problem will disappear for a day or two. The app connects to the SQL Server 200 SP4 database using ADO.
I suspected statistics might be at fault here but have tried both "UPDATE STATISTICS table WITH FULLSCAN" and "DBCC DBREINDEX('table') to no avail. This issue has occurred and been worked around in this manner a few dozen times.
SQL 7.0 SP1 - NT 4.00 EE SP6a - Cluster - Multiprocessor(2)-VB 6.0 SP4. Maybe a very stupid question,but I need to know if it is possible to assign a low priority to a VB exe that query for long time eating all the CPU (100% in task manager). Thank you. Franco
I have a pretty complex query that aggregates lots of data and inserts multiple rows of that data into a reporting table. When I call this SPROC from SQL Server Management Studio, it executes in under 3 seconds. When I try to execute the same SPROC using .NET's SqlCommand object the query runs indefinitely until the CommandTimeout is reached. Why would this SPROC behave differently with the same inputs, but being called from .NET? Thanks for your help!
HelloI am using an allready Full database MS SQL 2000my 3 tables -->Report :ReportID (PK)RNameRValueProduct :PNameCategoryReportID (FK)Infos :ICommentsIVaLuemy query (to get a new table with only columns, or a .NETcollection) -->SELECT Report.ReportID AS RID, Report.RName AS RN, Report.RValue AS RV, Infos.Commentar AS IC,MAX(CASE WHEN Product.Category = 50 THEN Product.PName END) AS P50, MAX(CASE WHEN Product.Category = 54 THEN Product.PName END) AS P54, MAX(CASE WHEN Product.Category = 78 THEN Product.PName END) AS P78, MAX(CASE WHEN Product.Category = 540 THEN Product.PName END) AS P540, MAX(CASE WHEN Product.Category = 1421 THEN Product.PName END) AS P1421FROM Report INNER JOIN Product ON Report.ReportID = Product.ReportID LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue WHERE (Report.ReportID = 10)GROUP BY Report.ReportID, Report.RName, Report.RValue, Infos.ICommentsReport.ReportID = Product.ReportID --> Primary Key to Foreign KeyReport.RValue = Infos.IValue --> only on full text (100 char)they are not indexedin Product can be a few million of lines, a few 10.000 in Report, about 1000 in Infosit can be very longhow can i do it in a better way ? (of course I cannot change the structure of tables, another aplication is using it)thank you
I've built a very strong form engine for one of our web apps. It's more featuristic that the TFS Work ITem Tracking.. and more complicated. In order to get the complexity It requires over 50 tables to support the 20+ fields I've created for the users.
The problem I have now is doing REports and Filtering on these tables.
Like TFS, it has querying... but I have a problem with storing the query. I have two columns right now:
ItemQuery TaskQuery.
The TaskQuery unions the task query and the ItemQuery and I can do sorting etc. on that. Without any constraints on the filter (say they jsut want to select column headers) the query can exceed 10,000 characters.
If someone wanted to have a constraint: I want to check if the CascadingDropDown "Strategic Plan" was ever "Maximize Potential" (or some other management term like that). That constraint sub-query is 500 characters.
Meaning, if someone put 20 constraints on a filter like that, I already have 10,000 characters.
Average Query without constraints: -I have my ItemQuery which contains the "columns" Query: 6,000 characters. -I have my TaskQuery which contains the "Columns" Query: 6,000 characters.
Which turns into:
SET @ItemQuery = (SELECT ...) SET @TaskQuery = (SELECT ...) EXEC(@ItemQuery + ' ' + @TaskQuery)
But now that I add constraints to those, I'm hooped.
I can create another column: ItemConstraint and TaskConstraint... but what if users create massive constraints and the query exceeds 8,000 characters, the Max a VARCHAR can hold...
I can't use TEXT as you can't create local variables of type TEXT in a stored procedure (DECLARE @Sample TEXT)... so I'm forced to use VARCHAR(8000).
Hi!How to create an alert that responses in case of long querya. I haven'tnoticed any counters that deals with these kind of situations?Thx for your help!
Below is my query which is taking a long time to execute, DB is SQL Server 2005 through a web Application I have downloaded the latest MS SQL 2005 driver 1.xxx and still the query takes long to execute
The Description field is a Full_text indexed catalog column the p.vendornumber is a primary key same with c.ID
Any one have an idea why it is taking this long to run
The Execution Time is: 13640 ms Which I think is very long
SELECT Upper(p.Type) Type,p.Modelname,p.partno,Upper(p.description) description, Upper(p.classification)classification,p.vendornumber,p.mfg, p.price,c.CompanyName,c.City,c.State,p.thumbnail FROM P_all p, Acts c WHERE p.vendornumber = c.ID AND CONTAINS(p.Description, '"helmet*"') Order by p.VendorNumber
I'm trying to optimize a long running (several hours) query. This query is a cross join on two tables. Table 1 has 3 fields - ROWID, LAt and Long. Table 2 has Name, Addr1,Addr2,City,State,Zip,Lat,Long.
Both tables has LatRad - Lat in radians, LonRad- Lon in Radians. Sin and Cos values of Lat and Lon are calulated and stored to be used in the distance formula.
What I'm trying to do here is find the nearest dealer (Table 2) for each of Table 1 rows. The Select statement takes a long time to execute as there are about 19 million recs on table 1 and 1250 rows in table 2. I ran into Log issues- filling the transaction log, so I'm currently using table variables and split up the process into 100000 recs at a time. I cross join and calculate the distance (@DistValues) and then find the minimum distance (tablevar2) for each rowid and then the result is inserted into another Table (ResultTable).
distance=3963.1*Case when cast(S.SinLat * T.SinLat + S.CosLat * T.cosLat * cos(T.Lonrad - s.Lonrad) as numeric(20,15)) not between -1.0 and 1.0 then 0.0 else acos(cast(S.SinLat * T.SinLat + S.CosLat * T.cosLat * cos(T.Lonrad - s.Lonrad) as numeric(20,15))) end
from dbo.TopNForProcess T , dbo.Table2 S where Isnull(T.Lat,0) <> 0 and Isnull(T.Lon,0)<> 0
Insert into @MinDistance
Select DataSeqno,Min(distance) From @DistValues Group by DataSeqno
Insert into ResultTable (DataSeqno,Lat2,Lon2,StoreNo,LAt1,Long1,distance)
Select D.DataSeqno, D.Lat2, D.Lon2, D.StoreNo, D.LAt1, D.Long1, M.distance from @DistValues D Inner Join @MinDistance M on D.DataSeqno = M.DataSeqno and D.Distance = M.Distance
I created a View called TopNForProcess which looks like this. This cut down the processing time compared to when I had this as the Subquery.
SELECT TOP (100000) DataSeqno, lat, Lon, LatRad, LonRad, SinLat, cosLat, SinLon, CosLon FROM Table1 WHERE (DataSeqno NOT IN (SELECT DataSeqno FROM dbo.ResultTable)) AND (ISNULL(lat, 0) <> 0) AND (ISNULL(Lon, 0) <> 0)
I have indexes on table table1 - Rowid and another one with Lat and lon. Table2 - Lat and Long.
Is there any way this can be optimized/improved? This is already in a stored procedure.
Hi all, I currently build a text processing database for my application. The database is to store all the plain text files submitted by customers. The plain text files will be processed before it store in the sql server. Thus I have 3 major tables Document, Term and TermDocument. I encounter a problem when I did a query. For example when I performed the following query
SELECT T.TermName, S.* FROM Weight AS S INNER JOIN Term AS T ON S.TermID = T.TermID WHERE T.TermName IN ('electronic','commerce','consists', ....)
The system told me that "No enough storage is available to complete the operation". The terms in "IN(....)" could have up 3k entries. I know this is huge, but I have no choice but to retrieve all of them in a single query.
Is there anyway to solve the problem or perform similar operation in alternative way.
Hello, I have to update a query that was given to me so that it displays only items that were created in a certain month and certain year (which I prompt the user for). I hard-coded a month and year to test (2008 year, 2 month). My results are including other months I'm not asking for. I have marked my problems areas with /* Problem 1 */ and /* Problem 2 */ (same where clause n 2 locations).|What am I doing wrong? Doesn't matter what year or month I enter, I am always getting extra data1st 3 columns of results (I removed the calculated fields) 2008 1 Incoming2008 1 Both2008 2 Outgoing2008 2 Incoming2008 2 BothI can't for the life of me figure out how to fix this up. query below... ================================ DECLARE @TheYear integerDECLARE @TheMonth integer SET @TheYear = 2008SET @TheMonth = 2 SELECT * FROM ( SELECT year(startime) as yearstart, month(startime) as monthstart, directioncodename , count(CASE WHEN new_issuecategoryname is null THEN activitycountvalue END ) as nullcall , count(CASE WHEN new_issuecategoryname='ACDelco Comment' THEN activitycountvalue END ) as acdelcocommentcall , count(CASE WHEN new_issuecategoryname='ACDelco General Inquiry' THEN activitycountvalue END ) as acdgeneralinquirycall , count(CASE WHEN new_issuecategoryname='ACDelco PPD'
THEN activitycountvalue END ) as acdppdcall , count(CASE WHEN new_issuecategoryname='ACDelco Promotion'
THEN activitycountvalue END ) as acdpromotioncall , count(CASE WHEN new_issuecategoryname='ACDelco Promotion - Calendar'
THEN activitycountvalue END ) as acdpromotioncalendarcall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Program Enquiry'
THEN activitycountvalue END ) as tssprogramenquirycall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Promotion'
THEN activitycountvalue END ) as tsspromotioncall , count(CASE WHEN new_issuecategoryname='ACDelco Training'
THEN activitycountvalue END ) as acdtrainingcall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Benefits'
THEN activitycountvalue END ) as tssbenefitscall , count(CASE WHEN new_issuecategoryname='ACDelco Other'
THEN activitycountvalue END ) as acdothercall , count(CASE WHEN new_issuecategoryname='ACDelco TAC Number'
THEN activitycountvalue END ) as acdtacnumbercall , count(CASE WHEN new_issuecategoryname='ACDelco PPD Promotion'
THEN activitycountvalue END ) as acdppdpromotioncall , count(CASE WHEN new_issuecategoryname='RealRewards - ISC'
THEN activitycountvalue END ) as realrewardsisccall , count(CASE WHEN new_issuecategoryname='RealRewards - CounterPerson'
THEN activitycountvalue END ) as realrewardscounterpersonscall , count(CASE WHEN new_issuecategoryname='ACDelco Event'
THEN activitycountvalue END ) as acdelcoeventcall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Lead'
THEN activitycountvalue END ) as acdtssleadcall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Other' THEN activitycountvalue END ) as acdtssothercall , count(CASE WHEN new_issuecategoryname is not null and new_issuecategoryname!='ACDelco Comment' and new_issuecategoryname!='ACDelco General Inquiry' and new_issuecategoryname!='ACDelco PPD' and new_issuecategoryname!='ACDelco Promotion' and new_issuecategoryname!='ACDelco Promotion - Calendar' and new_issuecategoryname!='ACDelco TSS Program Enquiry' and new_issuecategoryname!='ACDelco TSS Promotion' and new_issuecategoryname!='ACDelco Training' and new_issuecategoryname!='ACDelco TSS Benefits' and new_issuecategoryname!='ACDelco Other' and new_issuecategoryname!='ACDelco TAC Number' and new_issuecategoryname!='ACDelco PPD Promotion' and new_issuecategoryname!='RealRewards - ISC' and new_issuecategoryname!='RealRewards - CounterPerson' and new_issuecategoryname!='ACDelco Event' and new_issuecategoryname!='ACDelco TSS Lead' and new_issuecategoryname!='ACDelco TSS Other' THEN activitycountvalue END ) as othercall ,count(activitycountvalue) as totalcall
FROM ( select startime = CASE WHEN filteredphonecall.new_cmgstartdatetime is not null THEN filteredphonecall.new_cmgstartdatetime WHEN filteredphonecall.actualstart is not null and filteredphonecall.new_cmgstartdatetime is null THEN filteredphonecall.actualstart ELSE filteredphonecall.createdon END , 1 as activitycountvalue , new_issuecategoryname , new_issuecategory , 'phone call' as activitytypecodename , filteredphonecall.new_languagename , filteredphonecall.directioncodename from /* PROBLEM 1 */ filteredphonecall WHERE ( ( filteredphonecall.new_cmgstartdatetime is not null AND (Year(filteredphonecall.new_cmgstartdatetime) = @TheYear) AND (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth) ) OR ( filteredphonecall.actualstart is not null AND (Year(filteredphonecall.new_cmgstartdatetime) = @TheYear) AND (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth) ) OR ( filteredphonecall.actualstart is not null AND (Year(filteredphonecall.actualstart) = @TheYear) AND (Month(filteredphonecall.actualstart) = @TheMonth) ) OR ( filteredphonecall.createdon is not null AND (Year(filteredphonecall.createdon) = @TheYear) AND (Month(filteredphonecall.createdon) = @TheMonth) ) ) )as phoneactivities GROUP BY year(startime), month(startime), directioncodename
UNION ALL
/* KATHY1 */ SELECT year(startime) as yearstart , month(startime) as monthstart ,'Both' as directioncodename , count(CASE WHEN new_issuecategoryname is null THEN activitycountvalue END ) as nullcall , count(CASE WHEN new_issuecategoryname='ACDelco Comment' THEN activitycountvalue END ) as acdelcocommentcall , count(CASE WHEN new_issuecategoryname='ACDelco General Inquiry'
THEN activitycountvalue END ) as acdgeneralinquirycall , count(CASE WHEN new_issuecategoryname='ACDelco PPD'
THEN activitycountvalue END ) as acdppdcall , count(CASE WHEN new_issuecategoryname='ACDelco Promotion'
THEN activitycountvalue END ) as acdpromotioncall , count(CASE WHEN new_issuecategoryname='ACDelco Promotion - Calendar'
THEN activitycountvalue END ) as acdpromotioncalendarcall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Program Enquiry'
THEN activitycountvalue END ) as tssprogramenquirycall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Promotion'
THEN activitycountvalue END ) as tsspromotioncall , count(CASE WHEN new_issuecategoryname='ACDelco Training'
THEN activitycountvalue END ) as acdtrainingcall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Benefits'
THEN activitycountvalue END ) as tssbenefitscall , count(CASE WHEN new_issuecategoryname='ACDelco Other'
THEN activitycountvalue END ) as acdothercall , count(CASE WHEN new_issuecategoryname='ACDelco TAC Number'
THEN activitycountvalue END ) as acdtacnumbercall , count(CASE WHEN new_issuecategoryname='ACDelco PPD Promotion'
THEN activitycountvalue END ) as acdppdpromotioncall , count(CASE WHEN new_issuecategoryname='RealRewards - ISC'
THEN activitycountvalue END ) as realrewardsisccall , count(CASE WHEN new_issuecategoryname='RealRewards - CounterPerson'
THEN activitycountvalue END ) as realrewardscounterpersonscall , count(CASE WHEN new_issuecategoryname='ACDelco Event'
THEN activitycountvalue END ) as acdelcoeventcall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Lead'
THEN activitycountvalue END ) as acdtssleadcall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Other' THEN activitycountvalue END ) as acdtssothercall , count(CASE WHEN new_issuecategoryname is not null and new_issuecategoryname!='ACDelco Comment' and new_issuecategoryname!='ACDelco General Inquiry' and new_issuecategoryname!='ACDelco PPD' and new_issuecategoryname!='ACDelco Promotion' and new_issuecategoryname!='ACDelco Promotion - Calendar' and new_issuecategoryname!='ACDelco TSS Program Enquiry' and new_issuecategoryname!='ACDelco TSS Promotion' and new_issuecategoryname!='ACDelco Training' and new_issuecategoryname!='ACDelco TSS Benefits' and new_issuecategoryname!='ACDelco Other' and new_issuecategoryname!='ACDelco TAC Number' and new_issuecategoryname!='ACDelco PPD Promotion' and new_issuecategoryname!='RealRewards - ISC' and new_issuecategoryname!='RealRewards - CounterPerson' and new_issuecategoryname!='ACDelco Event' and new_issuecategoryname!='ACDelco TSS Lead' and new_issuecategoryname!='ACDelco TSS Other' THEN activitycountvalue END ) as othercall ,count(activitycountvalue) as totalcall
FROM ( select startime = CASE WHEN filteredphonecall.new_cmgstartdatetime is not null THEN filteredphonecall.new_cmgstartdatetime WHEN filteredphonecall.actualstart is not null and filteredphonecall.new_cmgstartdatetime is null THEN filteredphonecall.actualstart ELSE filteredphonecall.createdon END , 1 as activitycountvalue , new_issuecategoryname , new_issuecategory , 'phone call' as activitytypecodename , filteredphonecall.new_languagename , filteredphonecall.directioncodename from filteredphonecall /* PROBLEM 2 */ filteredphonecall WHERE ( ( filteredphonecall.new_cmgstartdatetime is not null AND (Year(filteredphonecall.new_cmgstartdatetime) = @TheYear) AND (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth) ) OR ( filteredphonecall.actualstart is not null AND (Year(filteredphonecall.new_cmgstartdatetime) = @TheYear) AND (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth) ) OR ( filteredphonecall.actualstart is not null AND (Year(filteredphonecall.actualstart) = @TheYear) AND (Month(filteredphonecall.actualstart) = @TheMonth) ) OR ( filteredphonecall.createdon is not null AND (Year(filteredphonecall.createdon) = @TheYear) AND (Month(filteredphonecall.createdon) = @TheMonth) ) ) )as phoneactivities GROUP BY year(startime), month(startime) ) as orderedresults order by yearstart, monthstart, directioncodename DESC;
I have a rather long SQL query to run with several date/time variables in it. The way I've done it in the past is to record a macro, paste the SQL into the query then afterwards turn the dates/times into variables by manually editing the code in the macro, so that next time it is run the SQL picks up the correct date/time and the correct data is retrieved. This has always worked okay in the past. However, the latest query I have is very long and complicated and I now appear to be unable to use the above method due, it appears, to the length of the query. It seems that the macro itself will only accept so many lines of an SQL query.
Please, does anybody know how to get round this problem?
Is it possible to save the query as an external query (.dqy) and also use variables within that query when a macro tries to run it? If so, how!? I've tried it, but it fails at the line ".Refresh BackgroundQuery:=False". With a general ODBC Error. I have put all of the necessary details in there, eg DSN etc, but no joy.
I've tried putting the SQL itself into several cells on a spreadsheet, altering the dates so that they a correct each time the query is run, concatenating the cells, turning the results into a variable and executing the SQL in a macro, but this also fails at the line ".Refresh BackgroundQuery:=False". Could this method be adapted?
Splitting the query into two shorter ones doesn't appear to be an option as there a loads of 'or' statements in it, several of which overlap. This would cause some results to be duplicated, which I would like to avoid!
Recently my system encounter some problem when retrieving certain record from MSSQL. For an example i have a database which contains 1.5 million of members. so i have a perl scripts that will execute to query based on certain range.
the schedule like below: 1 script - 1-250k (Query finish less than 5 mins) <interval 5 mins> 1 script - 250k-500k (Query finish less than 5 mins) <interval 5 mins> 1 script - 500k-750k (Query finish less than 5 mins) <interval 5 mins> 1 script - 750k-1M (Query finish in 1++ hours) <interval 5 mins> 1 script - 1M-1.25M (Query finish in 1++ hours) <interval 5 mins> 1 script - 1.25M-1.50M (Query finish in 1++ hours) END
After the 4th query, the query seems to work very slow, and this problem only raise on windows 2003 with mssql 2005, current server that run smoothly is win2k with mssql2000.
anyone have any idea on this problem either cause by operating system and database or related to something else?
dear guys. i have this one problem, where the sql statements really took very long time to be processed. It took more than 1 minute, depending on the total data in the table. I guest this have to do with the 'count' statements. here is the code:
------------------------------------------------------------ $sql = "SELECT company,theID,abbs,A as Active,N as Nonactive,(A+N) as Total FROM( select distinct D.nama As company, C.domID As theID, D.abbrew As abbs, count(distinct case when B.ids is NOT NULL THEN A.dauserid END) As A, count(distinct case when B.ids is NULL THEN A.dauserid END) As N FROM tableuser A LEFT OUTER JOIN tabletranscript B on (A.dauserid=B.dauserid) INNER JOIN thedommember C ON(C.entitybuktiID=1 AND C.mypriority=1 AND
C.entitybuktiID=A.dauserid) INNER JOIN mydomain D ON (C.domID=".$getID.") GROUP BY D.nama, C.domID, D.abbrew ORDER BY company )";
Hope any of you can simplify this statements into a query that doesnt take ages to be processed.
I have a CTE query that is used to fill in nulls on a history table. The With statement executes just fine. sub 2 seconds on 974 records, however the main query is what's turning the whole query into a turtle. I know that it's the looping that it's doing there that is causing the slow down, but I'm just not sure how to fix it. I've tried inserting it into a temp table, refactored the code a hundred times, but nothing seems to be working.
Code is below and the execution plan is attached. Server Version: 12.0.2342.0 Enterprise: 64bit
;WITH BuildTable AS ( SELECT [GEGTH].[ID] , [GEGTH].[Changed By] , CAST( [dbo].[GetWeekStarting] ([GEGTH].[Changed Date] , 2 ) AS DATE) AS WeekOf , [GEGT].[Title]
I am using the Dynamic Cross-Tab code supplied in an article from SQLServer Magazine (http://www.winnetmag.com/SQLServer/...608/15608.html).I modified the script to generate a temp table inside the storedprocedure, and then use this temp table as the source for thecross-tab. However, the problem seems to be that the dynamic SQLstring generated by the script is longer than what can be stored inthe @SQL variable. The Cross-tab works great, so long as the amount ofdata to be pivoted is small.Is there any way around this? E.g. a User defined type, or anotherdata type which can store more characters?Thanks,TimCREATE procedure CBN_CrossTab@StudyID varchar(100), --Model ID passed from web app - Only one modelcan be selected@Level int --The level to which the taxonomy should be rolled upAsDECLARE@Table as sysname, --Table to crosstab@OnRows as nvarchar(128), --Groupuing key values (on rows)@OnRowsAlias as sysname, --Alias for grouping cloumn@OnCols as nvarchar(128), --destination columns (on columns)@SumCol as sysname, --data cels@SQL AS varchar(8000), -- String to hold generated SQL String@NEWLINE as char(1) --Holds the New Line Character for the codeSET @OnRowsAlias = NullSET @SumCol = NullSET @NEWLINE = CHAR(10)-- Generate the Temp table for the taxa and countsCREATE TABLE #RefOrganisms (sampleid int, txtTaxa varchar(75),fltCount float)INSERT INTO #RefOrganisms(sampleid, txtTaxa, fltCount)SELECT dbo.tblsampledata.sampleid,dbo.CBN_RecursTaxa(dbo.tblbenthic.organism_tsn, @Level, " ") AS Taxa,SUM(dbo.tblbenthic.[count] /dbo.tblsitedetail.numberofreps) AS SumCountFROM dbo.tblstudylist INNER JOINdbo.tblsite ON dbo.tblstudylist.studyid =dbo.tblsite.study_id INNER JOINdbo.tblsitedetail ON dbo.tblsite.siteid =dbo.tblsitedetail.site_id INNER JOINdbo.tblsampledata ONdbo.tblsitedetail.sitedetailsid = dbo.tblsampledata.sitedetails_idINNER JOINdbo.tblbenthic ON dbo.tblsampledata.sampleid =dbo.tblbenthic.sample_id INNER JOINdbo.iter_intlist_to_table(@StudyID) i ONdbo.tblstudylist.studyid = i.number INNER JOINdbo.tblbenthictaxa ON dbo.tblbenthic.organism_tsn =dbo.tblbenthictaxa.tsnWHERE (dbo.tblsampledata.qaqc = 0) AND (dbo.tblsampledata.status =2) AND (dbo.tblbenthictaxa.rank_id >= @Level)GROUP BYdbo.tblsampledata.sampleid,dbo.CBN_RecursTaxa(dbo.tblbenthic.organism_tsn, @Level, " ")-- Identify the Temp table info for the CrossTabSELECT @Table = '#RefOrganisms'SELECT @OnRows = 'sampleid'SELECT @OnCols = 'txtTaxa'SELECT @OnRowsAlias = NullSELECT @SumCol = 'fltCount'--STEP1 BEGININNING OF SQL STRINGSET @sql = 'SELECT'+ @newline +' '+ @onrows +CASEWHEN @ONROWSALIAS IS NOT NULL THEN ' AS ' + @ONROWSALIASELSE ''ENDCREATE TABLE #KEYS(KEYVALUE NVARCHAR(100)NOT NULL PRIMARY KEY)DECLARE @KEYSSQL AS VARCHAR (1000)SET @KEYSSQL = 'INSERT INTO #KEYS ' + 'SELECT DISTINCT CAST(' +@ONCOLS + 'AS NVARCHAR(100)) ' + 'FROM ' + @TABLEEXEC (@KEYSSQL)DECLARE @KEY AS NVARCHAR(100)SELECT @KEY = MIN(KEYVALUE) FROM #KEYSWHILE @KEY IS NOT NULLBEGINSET @SQL = @SQL + ' ,'+ @NEWLINE +' SUM(CASE CAST(' + @ONCOLS +' AS NVARCHAR(100))' + @NEWLINE +' WHEN N''' + @KEY +''' THEN '+ CASEWHEN @SUMCOL IS NULL THEN '1'ELSE @SUMCOLEND + @NEWLINE +' ELSE 0' + @NEWLINE +' END) AS [' + @KEY + ']'SELECT @KEY = MIN(KEYVALUE) FROM #KEYSWHERE KEYVALUE > @KEYENDSET @SQL = @SQL + @NEWLINE +'FROM ' + @TABLE + @NEWLINE +'GROUP BY ' + @ONROWS + @NEWLINE +'ORDER BY ' + @ONROWSPRINT @SQL --+ @NEWLINE --FOR DEBUGEXEC (@SQL)GO
Hi there,I've a table with 18 millions of recordes shaped like this :Code nvarchar(80) , State int , school int , class int , Term nvarchar(80)The following query takes too long to run ( more than 2 hours )select State , school , class , term , count (term) as freqGroup by state , school , class , termHow may I speed up the query?My Pc is PIV (3.6 GHz) Intell , Win2003 Server , 512 MB of RAM, 80 GB of HDRegards,M.Mansoorizadeh
I'm running a query (see below) on my development server and its taking around 45 seconds. It hosts 18 user databases ranging from 3 MB to 400 MB. The production server, which is very similar but with only 1 25 MB user database, runs the query in less than 1 second. Both servers have been running on VMWare for almost 1 year with no problems. However last week I applied SP 2 to the development server, and yesterday I applied Critical Update KB934458. The production server is still running SQL Server 2005 Standard SP 1. Other than that, both servers are identical and running Windows 2003 Server Standard SP 1. I'm not seeing this discrepancy with other queries running against user databases.
use MyDatabase
GO
select db_name(database_id) as 'Database', o.name as 'Table',
s.index_id, index_type_desc, alloc_unit_type_desc, index_level, i.name as 'Index Name',
The query show below is designed to use seasonal profiles to compute 53 weeks of forecast data and then from that compute the number of weeks of supply of each item at each location. The query works but the volume of data produced (20+M rows) is substantial. If I limit the CTE to a single location, it run is 2 seconds and returns 41,000 rows. But when run for all locations and items, it runs for more than 4 hours. Would I do better converting the CTE to a sub-query and adding an index to improve the performance of the main query?
WITH Forecast AS
(SELECT Location_Idx
,Item_Idx
,Week_Code
,(CAST(AnnualQty AS DECIMAL(9))/53.0)*[Profile] AS fcst
FROM dbo.FactReplenishmentProfile rp
INNER JOIN dbo.FactSeasonalProfile sp
ON sp.SeasonalProfile_Idx = rp.SeasonalProfile_Idx
)
SELECT fcst1.Location_Idx
,fcst1.Item_Idx
,fcst1.Week_Code
,fcst1.fcst AS WeekQty
,SUM(fcst2.fcst) AS CumQty
FROM Forecast fcst1
INNER JOIN Forecast fcst2
ON fcst2.Location_Idx = fcst1.Location_Idx
AND fcst2.Item_Idx = fcst1.Item_Idx
AND fcst2.Week_code <= fcst1.Week_Code
GROUP BY fcst1.Location_Idx,fcst1.Item_Idx,fcst1.Week_code,fcst1.fcst
I'm using OLE DB model to insert long texts into ntext field.My code looks like :==BOOL COleDBRowData::Execute(CString strSQL){if(!m_bIsOpen)return FALSE;CCommand<CNoAccessor, CNoRowset>rs;HRESULT hr = rs.Open(m_session, strSQL);if(FAILED(hr))return FALSE;rs.Close();return TRUE;}==When strSQL length is over 5127XX bytes, Open function fails. Is thefunction supposed to be so? Is there any suggestion that I can avoidthis failure? I'm new to OLE DB thing.What is better is if somebody can tell me SQL command to insert a wholetext file content into the field directly. The sql command that I'mcurrently using is==INSERT INTO MyTable(Idx, Value) Values(N'{index}', N'{loooooongtext}')==Please help this poor soul. Thanks.--Daewon YOON
I have a long query which I have set off and would like to stop, andrename one of the tables used.My query is due to my lack of understanding of the underlyingstructure of MSSQL-Server...So say and update updates TABLE_A and I stop it, whilst thistransaction is rolling back I attempt to rename TABLE_A to TABLE_A_OLDand rename a different table to become TABLE_A. I am assuming thatthe rollback actions will use the object reference of TABLE_A_OLD andcontinue to rollback the effects on the correct table and not corrupt'new' TABLE_A... or will it not allow me to rename TABLE_A until therollback is complete?Thanks for any help!Steve
I am having a problem executing long running queries from an ASP applicationwhich connects to SQL Server 2000. Basically, I have batches of queries thatare run using ADO in a loop written in VBScript. This works pretty welluntil the execution time of a single query starts to exceed some threshold,which I am trying to narrow down. I can typically run 2 - 10 queries in aloop, with the run time being anywhere from under a minute to an hour ormore. Now that this application is being subjected to run against some largedatabases (25 - 40G), I'm having problems getting the application tocontinue beyond the first query if it takes a while to run.I used SQL Profiler to try to diagnose what was going on. I can see thequery executes to completion, but immediately after completing I can see an"Audit Logout" message, which apparently means that the client hasdisconnected. The query durations vary from 45 or 50 minutes to up to over90 minutes. I have the ADO connection and query timeouts set to very largevalues, e.g. 1000 minutes, so I can't think its that. My guess is that thereis some IIS setting or timeout that I am running up against and theconnection to SQL Server is just dropped for some reason.The configuration isNT 4.0 SP6SQL Server 2000 SP3IIS 4.0Internet Explorer 5.5I'm only running into this problem on the very largest databases we runagainst. The vast majority continue to function properly, but this is goingto happen more often as time goes on the databases continue to grow in size.Any advice is appreciated,-Gary