Long Query Alert
Jul 20, 2005Hi!
How to create an alert that responses in case of long querya. I haven't
noticed any counters that deals with these kind of situations?
Thx for your help!
ADVERTISEMENT
Alert SQL Failed Or Long Running Jobs?
May 17, 2004I guys,
Does Anyone of the SQLServer Guru has a smart script to alert DBA by email for failed jobs or jobs running more then their normal time(long running jobs), so that I dont have to go and look at the jobs everyday manually...on different servers...
Help is greatly appericated...
Thanks
Jessie.
Transact SQL :: Create Alert Email From Query
Jun 21, 2015How can I turn this query into an Email alert if one of the counts are GT 50. The Source are machines that I capture counts(Unprocessed_Cntr) from every 5 minutes and load to the Load_Time_Capture table. I also add a datetime to each capture(Unprocessed_Capture ).
If any individual source has a count > 50 or if the combined(ALL) GT 50 send an email to support person.
SELECT
CASE GROUPING([Source])
WHEN 1 THEN 'ALL'
ELSE [Source] END AS 'Input Source',
avg([Unprocessed_Cntr]) as 'Average Queue Past 15 Min'
FROM Load_Time_Capture
WHERE Unprocessed_Capture >= DateADD(mi, -15, Current_TimeStamp)
GROUP BY [source] WITH ROLLUP
Database Query... Novice Alert...No Idea Where To Turn...
Dec 18, 2006Hello,
I'm creating a website for a student organization at my university. Basically, I have an Excel file with a list of contacts that the members of our organization need to be able to search on our website.
The only field that I need our members to be able to search is "contact location." So for example, a member might want to know if our organization has any contacts in Spain, so the member would type "spain" into the "contact location search" field" and would get results.
I really don't known where to turn... I need the easiest thing that would be manageable for a novice. It doesn't need to be an intense search function; just something to make the website functional (so it obviously needs to be uploadable to a server; the server has PHP capability...no idea if that has any relevance). I've been searching around all day and have come across potentials like PHP, SQL, XML, etc. but have no idea which way to turn... I have Office and Adobe Studio programs at my disposal.
Thank you all-powerful and all-knowing web gods! I owe you my deepest gratitude!
Mikey
:beer:
Noob Alert! What I Thought Was A Real Simple Query...
Oct 22, 2007Hi
I have a table which has the column [itemNumber] Which contains numbers from 000 to 999. I have another table which has the UPC data for given items
I am trying to get results from my query that will show me every number in the itemNumberSet table that does not already exist (in the substring) of the UPCcode column.
By using the query below i am able to retrieve the opposite, and it works by returning results that do exist in the UPCcode column. But I cannot seem to get it to do the opposite which is what i am after. I figured it would be as simple as using NOT IN but that returned 0 results.
SELECT itemNumber FROM itemNumberSet
WHERE itemNumber IN (select SUBSTRING(UPCcode, 9, 3) FROM itemUPCtable)
ORDER BY itemNumber
Thanks for any suggestions you might have.
J
SQL 2012 :: How To Find Query Which Have Sort Warning Alert In Profiler
Jan 30, 2015which have a lot of impact to database performace since it do not fit onto memory and spill over to disks.
The question is when i ran a profiler and tried to catch a sort warning i cannot find the query which cause the alert.
Query Call By App Runs Long, Same Query In QA Doesn't.
Feb 19, 2008Hi Guys,
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.
Any idea what might be going on here?
Long Query - 100% CPU
Aug 10, 2001SQL 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
Long Running Query - Only From .NET
Aug 22, 2007I 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!
View 3 Replies View RelatedWhy Is The Query Running For So Long?
Feb 7, 2003When I execute the following stored procedure it runs for about a minute.
CREATE PROCEDURE EquipmentListByProduct
(
@iProdTypeId int
)
AS
SET NOCOUNT ON
DECLARE@iError int, @iRows int
SELECT pn.prodTypeId, pn.prodId, pn.prodName
FROM prodNames pn
WHERE pn.prodTypeId = @iProdTypeId
SELECT@iError = @@ERROR, @iRows = @@ROWCOUNT
IF ( @iError <> 0 )
BEGIN
RETURN@iError
END
IF ( @iRows = 0 )
BEGIN
RETURN-1
END
RETURN@iError
GO
The table only has 22 records.
Do I need to index the table? If so how do I do this?
A Too Long Query MAX(CASE WHEN
Sep 24, 2006HelloI 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
View 11 Replies View RelatedLong Dynamic Query
Apr 17, 2008I have to break dynamic query into two as it was more than 4000 characters long.
I have to use Execute(@nsql_1 + ' ' + @nsql_2) now
how can I use two parameters to use sp_executesql?
EXECUTE @error = sp_executesql @nsql, N'@min_date SMALLDATETIME,@max_date SMALLDATETIME',@min_date,@max_date
Thanks for help....
Help Saving A Very Long Query To DB
Aug 23, 2007I'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).
Any thoughts? We use SQL 2005.
Query Taking Too Long
Apr 3, 2007Below 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
Thanks
Long Running Query
Jun 2, 2006Hi,
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).
My Code looks like this:
Declare @DistValues table (DataSeqno varchar(10),Lat2 numeric(20,6),Lon2 numeric(20,6),StoreNo varchar(60), Lat1 numeric(20,6),Long1 numeric(20,6),Distance numeric(20,15))
Declare @MinDistance table (DataSeqno varchar(10) primary key,distance numeric(20,15))
Insert into @DistValues
Select DataSeqno,T.Lat Lat2,T.Lon Lon2,S.StoreNo,S.Lat Lat1,S.Long Long1,
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.
Thanks in advance.
A Long Query Problem
Jul 11, 2007Hi 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.
CPU Utilization Alert Through WMI Alert
Aug 13, 2015Can you use the below query to get CPU high utilisation alert purposes for both named and default instance? or, do I need to make any changes here (@wmi_namespace=N'.ROOTCIMV2' ) ?
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'CPU_WM_Utilization_Check',
@message_id=0,
@severity=0,
[code]....
Problem With My Where Clause (long Query)
Mar 1, 2008Hello,
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;
Excel, VBA And Long SQL Query Problem
Mar 2, 2003I 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!
Please, please help me!!
TIA
tobyjuggler
Long Query Takes Hours...
Jan 29, 2007Recently 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?
SQL Query Taking Too Long To Process
Jun 19, 2007dear 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.
Thanks in advance....
CTE Query - Long Load Time
Dec 5, 2014I 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]
[code]....
Dynamic Cross-Tab Query Too Long?
Jul 20, 2005I 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
View 1 Replies View RelatedSQL Server Takes Too Long To Run A Query
Jul 20, 2005Hi 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
View 6 Replies View RelatedDm Query Taking Long Time
May 16, 2007I'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',
avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages,
page_count, avg_page_space_used_in_percent, record_count,
ghost_record_count, min_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count,
schema_id, create_date, modify_date from sys.dm_db_index_physical_stats (null, null, null, null, 'DETAILED') s
join sys.objects o on s.object_id = o.object_id
join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id
where db_name(database_id) = 'MyDatabase'
order by avg_fragmentation_in_percent desc
--order by avg_fragment_size_in_pages desc
--order by page_count desc
--order by record_count desc
--order by avg_record_size_in_bytes desc
Large Long Running Query
Mar 20, 2008
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
DELETE Query Takes Long
Nov 15, 2007Hello,
i've got a table where a single DELETE query takes about 6 seconds.
The table has about 25 cols and 5 constraints (1PK, 4 FK). No triggers no cascade.
What can be the reason and what can i do to accelerate?
Thanks
[Edit]
- MS SQL Server 2000
- Running on LOCAL, 2.99 GHz Pentium
MSSQL Query Memory When Using Long Fields
Mar 3, 2006Here is our problem...We are doing a lot of selects against a table that has one large fieldin it.If we do a select against all the fields except for description, thequery comes back relatively quickly. If we add that last field (768chars) to the query, our query takes 10x longer (5 seconds vs 56seconds.)When we run the one without the description column, we can watchperfmon and see a very quick spike to physical disk. If we add in thedescription field we can see that the server becomes I/O bound - thedisk sits at 100% until the query is complete.We have tweaked the min query memory setting for the server but itseems to have had no effect no matter how high we set it. Is theresome point at which MSSQL decides it cannot perform the transaction inmemory? What would I increase to cure this problem?For example:TMZDIFF int410WRITETIMEcharno 16System_Namecharno 64Timestampcharno16Name charno32Mount_Pointcharno32Size intno4 10Space_Usedintno410Space_Availableintno410Inode_Sizeintno410Inodes_Usedintno410Inodes_Freeintno410Space_Used_Percentintno410Inodes_Used_Percentintno410FS_Type charno8Space_Available_Percentintno410Name_U ncharno32Descriptionncharno768
View 2 Replies View RelatedProblem With CCommand::Open When Query Str Is Too Long.
Dec 20, 2006I'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
View 1 Replies View RelatedLong Query Rollback - Can I Rename The Table?
Mar 8, 2007I 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
View 1 Replies View RelatedADO Client Disconnects After Running A Long Query
Jul 20, 2005I 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
View 4 Replies View RelatedEXtremely Long Time In Execution Query
Mar 21, 2007Hi all,
I have a query, rather complex one to deal with more than 1 million rows, used to run 40 minutes in SQL Server 2000 in query analyzer. Now, it has been 10 hours in SQL Server 2005 in management studio. And still has not finished yet! Anything can go wrong here. Basically nothing changes, except for I have my server upgrade from SQL Server 2000 to SQL Server 2005. Seems something is wrong crazy in SQL Server 2005. Any suggestions?
Thanks,
Ning
Simple Select Query Takes A Very Long Time
Oct 11, 2006I have a table tblCustTrans which contains
custid int
transid int
startdate datetime
value int
the custid, transid and startid are composite primary key.
the table contains more than 10 million records. Now i want to fetch record for
select * from tblcusttrans where startdate > = 10/10/2006 10:00:000 and startdate <= 10/10/2006 11:00:000
This statement is taking more than 2 hours to fetch the data. is there a way to fetch the record with less time
Regards