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
Hi 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?
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!
View 3 Replies View RelatedWhen 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?
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
View 11 Replies View RelatedI 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....
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).
Any thoughts? We use SQL 2005.
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!
View 3 Replies View RelatedBelow 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
Hi,
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.
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!
Please, please help me!!
TIA
tobyjuggler
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.
Thanks in advance....
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]
[code]....
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
View 1 Replies View RelatedHi 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 RelatedI'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
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
Hello,
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
Here 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 RelatedI'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 RelatedI 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 RelatedI 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 RelatedHi 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
I 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
Hi,
we've created a ata Flow task to execute several aggregations. Our Task access database using OLE DB source and selects data out of our staging tables (we've analyzed the query using MS SQL Management Studio which didn't showed any issues). But when we try to run our dataflow task using SSIS (debug mode and DTEXEC from command line) we experince that tasks seem to stop during processing.
Unfortunately we didn't found a way to see long logfile entries which explain the issue to us.
We do use several aggregation tasks divided in 4 sequences. Unfortunately we just see one logical processor out of 4 logical processors working. It is a Windows 2003 SP2 machine with SQL 2005 SP2 on top of it.
Is there any solution to use all processors to one package for parallel execution?
So basically we experience two issues:
- SSIS seems to stop somewhere in thre middle
- SSIS just uses one processor instaed of all four
your advice is appreciated
hi,
i have worked three days on a query to display all my results in a beautiful report. The query is fine because when i execute it in Query Analyzer i have all results i want to see in my statistics-table in my report...
One thing: it's contains about 100 unioned statements, which results in a super-long query. Performance is OK because it are all 100 very easy statements that are union-ed together.
But, when I copy-paste it in my query-string window/textbox of the report designer, I see that there's a maximum on that textbox lenght, which results in the fact that my long query suddenly stops.
Any solutions????
We have been bothered with this problem for a while. Usually I happens in the early moring. Later on after the error is gone on auser, the error never happens again on any user for the day. Is this a web Server problem or an aspx.vb coding error? Thanks,Jeffrey
Server Error in '/SSSSS' Application.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.Source Error:
Line 270: oDBCommand.Connection.Open()
Line 271:
Line 272: Dim myReader As SqlDataReader _
Line 273: = oDBCommand.ExecuteReader(CommandBehavior.CloseConnection)
Line 274:Source File: E:SSSSSScheduling.aspx.vb Line: 272 Stack Trace:
[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +862234
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739110
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
System.Data.SqlClient.SqlDataReader.get_MetaData() +62
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +903
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +62
Soma.Scheduling.GetNextAutoAppointment() in E:SSSSSAScheduling.aspx.vb:272
Soma.Scheduling.Page_Load(Object sender, EventArgs e) in E:SSSSSScheduling.aspx.vb:61
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +47
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061
I have a simple update statement that is running forever in SQL 2005 but works fine in SQL 2000. We have a new server we put SQL 2005, restored db. The table in question WEEKLYSALESHISTORY I even re-indexed all the indexes and rebuilt the stats as well. But still no luck, still running extremely long. 1 hour 20 minutes.
I'll try to give you some background on these table. Weeklysalehistory has approx 30 fields. I have 11 indesxes set up weekending date being one of them. And replication control has index on lasttrandatetime as well. So I think my indexes are fine.
/* Update WeekEnding Date for current weeks WeeklySales Records */
Update WeeklySalesHistory set
weekendingdate =
(SELECT LastTransDateTime from ReplicationControl
where TableName = 'WEEKHST')
where weekendingdate is null
Weekly sales has approx 100,000,000 rows
Replication control has 631,000 (Ithink I can delete some from here to bring it down to 100 or 200 records) Although I don't think this is issue since on 2000 has same thing and works fine.
I was trying to do this within SSIS and thought that was issue. I am new so SSIS but it runs long even if I just run it as a job with this simple Update statement so I think its something with tables, etc that is wrong.
One thing on noticed if I look at the statistics in SQL Server Management studio there is a ton of stats. some being statistics on indexes which makes sense then I have a ton of hind_113_9_6 and simiiar one like this. I must have 90 or so named like this. Not sure how to check on SQL 2000 all the stats to see if they moved over from there or what. I checked a few other tables and don't have all these extra stats. Could this be causing the issue do I need to delete all these extras? Any help would be greatly appreciated.
Stacy
Hi,
I have problem with JDBC 2005 (1.1) running against SQL 2005 Express edition (SP2). Sometimes, the statement takes long time (more than 10 seconds). Sometimes, the same statement takes just a few seconds. It is very unpredictable.
The query that we have problem is most of the time is join sql statement.
Does anyone see this problem?
Thanks,
i use a single stored procedure to update many tables in sql server 2014 database, using defalut transaction isolation level we got random performance issues.
maybe it would better to use read uncommitted isolation level?
what's happens ,in the both cases( read committed, uncommitted) if the sp is called at the same time passing the same @key parameter?
this is a sample to show of the real stored procedure works:
CREATE PROCEDURE SP_Test
@Key int,
@Values1 Values1 readonly,
@Values2 Values2 readonly,
@Values3 Values3 readonly
[code]....