Hi, I have problem running this query. It will time out for me...
My database are small just about 200 members.
I have a site for swaping appartments (rental). my query should look
for match
in a triangle. Like this member A -> B->C
A give his appartment to B. B gives his appartment to C and finally
C gives his appartment to A
Soo my query looks for matching parameters like rooms, location, size
and
soo on..
I have one table for existing appartments and one for "whanted
appartments"
and 1 table called "intresse" where members can store "yes" or "no" if
they are interessted in a appartment.
I also have a table called "omrade" to store locations of interest.
Hope you can helpe me with some tip soo i can run this query in a few
seconds instead of 20-30 sec
Thanks M
SELECT
F.medlemsNr as medlemsNr, F.lfId AS lfId, F.ort AS ort, F.gatuadress
AS gatuadress, F.gatuNr AS gatuNr, F.rum AS rum,F.storlek AS
storlek,
F.hyra AS hyra, count(F.medlemsNr) As hits
FROM
medlem08 A, medlem08 B, medlem08 C, lagenhetF08 D,
lagenhetO08 E, lagenhetF08 F, lagenhetO08 G, lagenhetF08 H,
lagenhetO08 I
WHERE
D.rum >= I.rumMin AND D.rum <= I.rumMax AND
D.storlek >= I.storlekMin AND D.storlek <= I.storlekMax AND
I.hyraMax = 0" & " OR D.hyra <= I.hyraMax) AND
I.balkong = '" & "" & "' OR D.balkong = I.balkong) AND
(I.badkar = '" & "" & "' OR D.badkar = I.badkar) AND
(I.bredband = '" & "" & "' OR D.bredband = I.bredband) AND
(I.hiss = '" & "" & "' OR D.hiss = I.hiss) AND
(I.spis = '" & "" & "' OR D.spis = I.spis) AND
(I.brf = '" & "" & "' OR D.brf = I.brf) AND
D.postNr IN (select postNr from ONSKEMAL08 where loId=I.loId) AND
F.medlemsNr Not IN (select medlemsNr2 from INTRESSE08 where
medlemsNr1=A.medlemsNr) AND
H.rum >= G.rumMin AND H.rum <= G.rumMax AND
H.storlek >= G.storlekMin AND H.storlek <= G.storlekMax AND
(G.hyraMax = 0" & " OR H.hyra <= G.hyraMax) AND
(G.balkong = '" & "" & "' OR H.balkong = G.balkong) AND
(G.badkar = '" & "" & "' OR H.badkar = G.badkar) AND
(G.bredband = '" & "" & "' OR H.bredband = G.bredband) AND
(G.spis = '" & "" & "' OR H.spis = G.spis) AND
(G.brf = '" & "" & "' OR H.brf = G.brf) AND
H.postNr IN (select postNr from ONSKEMAL08 where loId=G.loId) AND
F.rum >= E.rumMin AND F.rum <= E.rumMax AND
F.storlek >= E.storlekMin AND F.storlek <= E.storlekMax AND
(E.hyraMax = 0" & " OR F.hyra <= E.hyraMax) AND
(E.balkong = '" & "" & "' OR F.balkong = E.balkong) AND
(E.badkar = '" & "" & "' OR F.badkar = E.badkar) AND
(E.bredband = '" & "" & "' OR F.bredband = E.bredband) AND
(E.hiss = '" & "" & "' OR F.hiss = E.hiss) AND
(E.spis = '" & "" & "' OR F.spis = E.spis) AND
(E.brf = '" & "" & "' OR F.brf = E.brf) AND
F.postNr IN (select postNr from ONSKEMAL08 where loId=E.loId) AND
A.medlemsNr=D.medlemsNr AND A.medlemsNr=E.medlemsNr AND
B.medlemsNr<>A.medlemsNr AND C.medlemsNr<>A.medlemsNr AND
B.medlemsNr<>C.medlemsNr AND
B.sparr<>1 AND C.sparr<>1 AND
A.typ=11 AND A.medlemsNr=" & session("medlemsNr") & " AND
B.medlemsNr=F.medlemsNr AND B.medlemsNr=G.medlemsNr AND
B.typ=11 AND A.triangel=1 AND B.triangel=1 AND C.triangel=1 AND " &_
C.medlemsNr=H.medlemsNr AND C.medlemsNr=I.medlemsNr AND
C.typ=11 group by F.lfId, F.medlemsNr,F.ort,
F.gatuadress,F.gatuNr,F.rum,F.storlek,F.hyra
I am running the query below but I am getting the following results:-
Store Prod_no Quant_Sold Total Orders Total_Sale 115 M1015 4 4 4000 115 M1015 2 1 2000
For some reason on some of the products there is more than one entry for that day per store. As you can see above there is two entries for the product M1015. I need the results to look like the below:
Store Prod_no Quant_Sold Total Orders Total_Sale 115 M1015 6 5 6000
SELECT
store.branch_no AS store_no,
crea_date,
jobstock.prod_no,
((jobstock.quantity)*COUNT(jobstock.job_id)) AS quantity_sold,
COUNT(jobstock.job_id) AS total_orders,
SUM(jobstock.total*100) AS store_total,
cast (SUM(jobstock.total/1.25)*100 AS INTEGER) AS store_total_before_tax,
((((stock.curr_cost*jobstock.quantity)*100)*COUNT(jobstock.job_id))) AS cost
FROM store, pickup, jobstock, stock WHERE (store.store_id=pickup.store_id) AND (pickup.job_id=jobstock.job_id) AND (stock.prod_no=jobstock.prod_no) AND (stock.store_id=pickup.store_id) AND branch_no>0 and crea_date between '2008-02-25' and '2008-02-28' GROUP BY store.branch_no, jobstock.prod_no, jobstock.quantity, stock.curr_cost, crea_date ORDER BY crea_date
-- Get the new Customer Identifier, return as OUTPUT param SELECT @NoteID = @@IDENTITY
-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned -- users. IF @FK_UserIDList IS NOT NULL EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList
-- Insert New Address record -- Retrieve Address reference into @AddressId -- EXEC spInsertForUserNote -- @FK_UserID, --@NoteID, -- @BeenRead -- @Fax, -- @PKId, -- @AddressId OUTPUT
COMMIT TRANSACTION
-------------------------------------------------- GO
I converted an Access db to SQL Server 2005 Express using FullConvert Enterprise and the conversion went well. I ran a few queries. When I logged in today and run the same exact query (saved it) I get this error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tblTXMup'.
I googled the error and someone in another forum said to check ownership, that it would throw that error if another user who was not dbo tried to run a query. I checked and the owner is sa. I logged in as sa and get the same error.
if have a problem with a SELECT query that works very fine on SQL Server 2000 but not on 2005. I've transfered my db by creating a full backup and restoring the db on 2005. The db is working except this problem.
When i start executing it doesn't finish. I waited a couple of minutes. On 2000 it only takes about 6 seconds to run.
Here it is:
Code Snippet
SELECT * FROM PPS_TERMbesttmpwhere PPS_TermBestTmp.BestNr + CONVERT(varchar(30),CAST(REPLACE(PPS_TermBestTmp.Pos1, ',', '.') AS float),2)
NOT IN (SELECT PPS_TermBest.BestNr + CONVERT(varchar(30), PPS_TermBest.Pos1,2) FROM PPS_TermBest) Any ideas?
Mine Below Query takes considerable time at the time of execution. Can any one help me, what is the other way to write this query?
Declare @p_Mkt_View_Id int Set @p_Mkt_View_Id = 17
Select Distinct Customer_id From Active_Product_Cust_Dtl Where Product_Group_Code in (Select Distinct Product_Group_Code From Products Where Product_code in ( select Distinct ProductId from pit where pitid in (select pitid from marketviewdef where mktviewid = @p_Mkt_View_Id)))
Hi have this query which is taking too much time to execute..........I have tried the following options but not useful till now 1. NOLOCK 2. SET NO ROW COUNT 3. CHANGED DISK LOCATION OF TEMPDB 4. CHECKED %Processor Time 5. Checked pages.sec
Below is the query, any suggestions will be really helpful
SE IFRepository --Query --Returns count of txns whose status is not (10001 or 10002) declare @fileruntimeuid int declare @Pendingackcount int set @Pendingackcount =0 set @fileruntimeuid =0 declare @clientname varchar(256) set @clientname = NULL declare @txncnt int set @txncnt = 0 declare @FileNameClient varchar(256) set @FileNameClient = NULL
declare @StageStatus int set @StageStatus = 0 declare @StageDesc varchar(35)
declare PendingAcks cursor for select distinct fileruntimeuid from tiffileruntime WITH (NOLOCK) where filecreationdt >= convert(smalldatetime,'9-11-07') and filecreationdt <= convert(smalldatetime,'9-12-07') and statusid <> 2 --and filetypeuid in (1,8,16,17,18) --and clientuid =1205 order by fileruntimeuid --244873, 244883, 244885, 244892, 244893, 244925, 244926, 244966, 244967, 244873, 244883
Print 'File Life Cycle Viewer via Database' Print '===========================================================================================' Print 'Status FileRuntimeUID Client Name File Status File name' Print '===========================================================================================' Open PendingAcks FETCH NEXT FROM PendingAcks into @fileruntimeuid WHILE @@FETCH_STATUS = 0 BEGIN select top 1 @StageDesc = b.IFComponentDesc from TIFComponent b, TIFFIleProcessingStatus a WITH (NOLOCK) where a.IFComponentUID = b.IFComponentuid and a.fileruntimeuid = @fileruntimeuid order by a.FPROCStageStartDt desc-- a.IFComponentUID desc
select @clientname = ClientShortName from tifclientattrib where clientuid = (select clientuid from tiffileruntime where fileruntimeuid = @fileruntimeuid)
select @StageStatus = statusid, @FileNameClient= FileNameClient from tiffileruntime where fileruntimeuid = @fileruntimeuid select @txncnt = FProcTxnProcessedInTotal from tiffileprocessingstatus where fileruntimeuid = @fileruntimeuid and IFComponentUID = 5 --if @StageDesc = "" Begin @StageDesc = "------------" End print RTRIM(convert(varchar(10),@StageStatus)) + ' ' + RTRIM(convert(varchar(10),@fileruntimeuid)) + ' ' + RTRIM(@clientname) + ' ' + @StageDesc + ' ' + RTRIM(@FileNameClient) set @StageDesc = NULL FETCH NEXT FROM PendingAcks into @fileruntimeuid END
Print '===========================================================================================' close PendingAcks deallocate PendingAcks
I want to take the execution plan of some transact sql queries, I took the execution plan as text based one, that having
index scan, index seek
Remote scan, remote Update
sort order by cluases
in the above clauses what is the high performance, and how will i change to the high performance clauses by changin the query to improve the execution speed of the query
Please guide me
the execution plan is
SELECT [Inventory_Profile].[InventoryID] ,[Inventory_Profile].[Alias] ,[Inventory_Profile].[InventoryStatusID] ,[Inventory_Profile].[InventorySubTypeID] ,[Inventory_Profile].[InventoryTypeID] ,[Inventory_Profile].[AcquisitionDate] ,[Inventory_Profile].[UnitNumber] ,[Inventory_Profile].[YearOfManufacture] ,[Inventory_Profile].[Manufacturer] ,[Inventory_Profile].[Make] ,[Inventory_Profile].[Model] ,[Inventory_Profile].[SerialNumber] ,[Inventory_Profile].[UsageConditionID] ,[Inventory_Profile].[Description1] ,[Inventory_Profile].[Description2] ,[Inventory_Profile].[LocationEffectiveFromDate] ,[Inventory_Profile].[IsFlaggedForSale] ,[Inventory_Profile].[RentalPurchaseOrderNumber] ,[Inventory_Profile].[AquisitionPurchaseOrderNumber] ,[Inventory_Profile].[SortOrder] ,[Inventory_Profile].[IsSaleLeaseBack] ,[Inventory_Profile].[InterimRentReceivableUpfrontTaxModeID] ,[Inventory_Profile].[LeaseRentalReceivableUpfrontTaxModeID] ,[Inventory_Profile].[OverTermReceivableUpfrontTaxModeID] ,[TaxDepreciation_Inventory].[IsTaxDepreciationRequired] ,[TaxDepreciation_Inventory].[IsComputationPending] ,[TaxDepreciation_Inventory].[TaxDepreciationTemplateID] ,[TaxDepreciation_Inventory].[InventoryCostBasisAmount] ,[TaxDepreciation_Inventory].[DepreciationBeginDate] ,[TaxDepreciation_Inventory].[DepreciationEndDate] ,[TaxDepreciation_Inventory].[IsTaxDepreciationTerminated] ,[TaxDepreciation_Inventory].[IsStraightLineMethodUsed] ,[TaxDepreciation_Inventory].[IsLeaseTermUsedForStraightLineMethod] ,[Inventory_PTMS].[Division] ,[Inventory_PTMS].[Branch] ,[Inventory_PTMS].[SalesTaxPercent] ,[Inventory_PTMS].[SalesTaxAmount] ,[Inventory_PTMS].[IsSalesTaxIncluded] ,[Inventory_PTMS].[GLExpenseAccount] ,[Inventory_PTMS].[GLAssetAccount] ,[Inventory_PTMS].[SoftwareExclusionAmount] ,[Inventory_PTMS].[AssetCategoryCodeID] ,[Inventory_PTMS].[OwnershipCodeID] ,[Inventory_PTMS].[ManufacturingCodeID] ,[Inventory_PTMS].[ReimburseCodeID] ,[Inventory_PTMS].[BillingStatusID] ,[Inventory_PTMS].[PropertyTaxExemptionCodeID] ,[Inventory_PTMS].[UserDefinedField1] ,[Inventory_PTMS].[UserDefinedField2] ,[Inventory_PTMS].[Notes] FROM [Inventory_Profile] INNER JOIN [TaxDepreciation_Inventory] ON [Inventory_Profile].[InventoryID]=[TaxDepreciation_Inventory].[InventoryID] INNER JOIN [Inventory_PTMS] ON [Inventory_Profile].[InventoryID]=[Inventory_PTMS].[InventoryID] INNER JOIN [Inventory_Status_CnfgLocale] ON [Inventory_Profile].[InventoryStatusID] in (SELECT InventoryStatusID FROM Inventory_Status_CnfgLocale WHERE InventoryStatusName <> 'Donated' and InventoryStatusName <> 'Scrap' and InventoryStatusName <>'Write Off' and InventoryStatusName <> 'Sold')
I have the following query which works fine when it's executed as a single query. but when i union the result of this query with other queries, it returns a different set of data.
any one know why that might be the case??
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone where contact.phone_num is not null and contact.status = 'New Contact' group by contact._id order by "recency" desc, "frequency" desc
i've included the union query here for completeness of the question
begin declare @current_date datetime set @current_date = GETDATE()
select top 100 _id, callback_date, priority, recency, frequency from ( ( select top 10 _id, callback_date, 10 priority, @current_date recency, 1 frequency --, DATEPART(hour, callback_date) "hour", DATEPART(minute, callback_date) "min" from topcat.class_contact where status ='callback' and (DATEPART(year, callback_date) <= DATEPART(year, @current_date)) and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) -- all call backs within that hour will be returned and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date)) and (DATEPART(hour, callback_date) <> 0) order by callback_date asc --order by priority desc, DATEPART(hour, callback_date) asc, DATEPART(minute, callback_date) asc, callback_date asc ) union ( select top 10 _id, callback_date, 9 priority, @current_date recency, 1 frequency from topcat.class_contact where status = 'callback' and callback_date is not null and (DATEPART(year, callback_date) <= DATEPART(year, @current_date)) and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date)) and (DATEPART(hour, callback_date) = 0) order by callback_date asc ) union ( select top 10 _id, callback_date, 8 priority, @current_date recency, 1 frequency from topcat.class_contact where status = 'No Connect' and callback_date is not null and (DATEPART(year, callback_date) <= DATEPART(year, @current_date)) and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date)) order by callback_date asc ) union ( select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone where contact.phone_num is not null and contact.status = 'New Contact' group by contact._id order by "recency" desc, "frequency" desc ) ) contact_queue order by priority desc, recency desc, callback_date asc, frequency desc
I am able to select the average, max, etc of the variable in a simple select statement but when I do the following, it doesn't work. The reason I am doing the following is because I am calculating the average and such over a 5 min interval, then saving the output as one line. That is why I condense average, max, min, and stdev into one variable which I then output. why it won't run when I have it like this but will run when it is like this?
will run:
SELECT AVG(NacTemp), MAX(NacTemp),MIN(NacTemp), STDEV(NacTemp) FROM [DATABASE] WHERE [UTCDeviceTimeStamp] between DATEADD(minute, -5, GETUTCDATE()) and GETUTCDATE()
won't run but I need it to:
DECLARE @now datetime SET @now = GETUTCDATE() --Universal Time DECLARE @timeint int SET @timeint = '5' --time interval in minutes DECLARE @time datetimeSET @time = (SELECT MIN([UTCDeviceTimeStamp]) FROM [DATABASE] WHERE [UTCDeviceTimeStamp] BETWEEN DATEADD(minute, -@timeint,@now) AND @now) -- Timestamp data will be saved as DECLARE @comma varchar(4) SET @comma = ', '
I have a query which used to run fine on a rubbish SQL 2000 box in about a minute, but with SQL 2005 (SP2) it never finishes, even when left overnight. No errors in the logs or anything. It is the same database which was backed up from SQL 2000 and restored into 2005. Does anybody have any ideas?
Cheers Steve
SELECT DISTINCT R1.RowVersionId, R2.EnumID AS A, R2.EnumID AS B, R4.EnumID AS C, R6.EnumID AS D, R8.EnumID AS E, R10.EnumID AS F, R12.EnumID AS G, R14.EnumID AS H
FROM
RowRuns AS R1
INNER JOIN XRunConfigEnum AS R2 ON R1.RunVersionID = R2.RunVersionId
INNER JOIN RowRuns AS R3 ON R1.RowVersionId=R3.RowVersionId
INNER JOIN XRunConfigEnum AS R4 ON R3.RunVersionID = R4.RunVersionId
INNER JOIN RowRuns AS R5 ON R1.RowVersionId=R5.RowVersionId
INNER JOIN XRunConfigEnum AS R6 ON R5.RunVersionID = R6.RunVersionId
INNER JOIN RowRuns AS R7 ON R1.RowVersionId=R7.RowVersionId
INNER JOIN XRunConfigEnum AS R8 ON R7.RunVersionID = R8.RunVersionId
INNER JOIN RowRuns AS R9 ON R1.RowVersionId=R9.RowVersionId
INNER JOIN XRunConfigEnum AS R10 ON R9.RunVersionID = R10.RunVersionId
INNER JOIN RowRuns AS R11 ON R1.RowVersionId=R11.RowVersionId
INNER JOIN XRunConfigEnum AS R12 ON R11.RunVersionID = R12.RunVersionId
INNER JOIN RowRuns AS R13 ON R1.RowVersionId=R13.RowVersionId
INNER JOIN XRunConfigEnum AS R14 ON R13.RunVersionID = R14.RunVersionId
WHERE
((R2.ParamID='ee72510e-3bab-49f6-1ff9-4d09cbe8670a' AND (R2.EnumID = '1a2868fb-72ef-e1d3-e79d-fbb5814ab481')))
AND
((R4.ParamID='7aadb3a4-3d13-8e0d-bfa4-4243ed1fdb35' AND (R4.EnumID = '745fb00c-0b16-7b4e-bf8f-da0f46777ca0')))
AND
((R6.ParamID='8c9aee3a-df1f-6ec5-131a-8fa0309ce1ff' AND (R6.EnumID = 'c7af1456-56bc-ba9c-f1e4-95cfd5542d10')))
I have created a lot of reports using this technique, but this is the first one that doesn't work. As there is absolutely nothing special about it, I can't figure out what the issue is.
I have one dataset that uses parameters chosen from two other dataset results. One dataset result runs just fine and returns values in reporting services, but the other returns blank in reporting services. In Visual Studio, both datasets return data.
I cannot for the life of me figure out what I've done that gives this result as I've never encountered it before and use this method of parametization quite frequently.
The dataset in question doesn't even join any tables, it's a direct select distinct field"1" from table"a".
We have one LEDGER, where all the daily activities are stored. The LEDGER table has 4 indexes (1 clustered and 3 non-clustered). To get AR we use this table.
Well problem is some times in 1-2 months, any simple AR query takes a long time and every other client gets slow response (queries are very slow or sometimes block).
If we DROP any index on LEDGER table and again put it back (RECREATE), all our queries work fine and faster. This goes on till 1-2 months, till we see the same issue again.
This is a classic case happened today. Queries were running fine till morning 8 AM. We upload some 50 thousand records to Ledger table (Data Conversion). Well after 30 mins, all simple AR queries started taking a long time. We DROPPED an index in LEDGER table and everything was faster....Just to be same we added back the same index again.......everything is Faster.....
What is this. ....is it our QUERY, index or huge Transactions or no free space ???
We are scheduled to run SP4, next week. But is there any solution in the mean time on what is this?
Also is they any way to KILL all SQL server processes that take more than a mins. We just don't want ALL our client to Slow down because of one query????
I have 2 servers: myLocalServer (SQL2005) and myRemoteServer (SQL2000), both in the same LAN. I wish to syncronize a remote table with a local table (both share the same structure) by means of a stored procedure. The amount of rows to carry from the local to the remote table is about 20,000. The query takes more than a minute, and I would like to take down that time. Can you please help me?
myRemoteServer is declared in myLocalServer by means of a Linked Server object, and I declared a synonym called Syn_RemoteTable which represent the remote table.
First I tried a cursor, but it did not worked:
declare curLocalTable cursor local forward_only static read_only for select ID, Value from myLocalTable where UpdateTimeStamp>@LastUpdate
open curLocalTable fetch curLocalTable into @ID, @Value
while @@Fetch_Status=0 begin if exists(select ID from Syn_RemoteTable where ID=@ID) begin update Syn_RemoteTable set Value=@Value where ID=@ID end else begin insert into Syn_RemoteTable (ID, Value) values (@ID, @Value) end fetch curVentasMensuales into @ID, @Value end
close curLocalTable deallocate curLocalTable
Other way that I tried -performing equally poorly- was:
update Syn_RemoteTable set Value=T.Value from Syn_RemoteTable inner join ( select ID, Value from myLocalTable where UpdateTimeStamp>@LastUpdate ) as T on T.ID=Syn_RemoteTable.ID
insert into Syn_RemoteTable ( ID, Value ) select ID, Value from myLocalTable where UpdateTimeStamp>@LastUpdate and ID not in (select ID from Syn_RemoteTable)
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.
SELECT * FROM TA a WHERE a.rx=264886 and AN= (select max(AN) FROM TA where rx=a.rx)
I have a table TA with 8+ million rows and there is clustered PK on (rx, AN) columns. The count on rx=264886 is 6000+ rows. This query takes about 1 to 2 minutes to fetch data. Can anyone suggest how to improve performance and fetch data faster?
I am doing sp tuning. It has several lines. SO I divided into several small queries and executed individually and check the execution plans. In one small query, I found table scan is happening. That query is basically retrieving all columns from a table but the table doesn't have any pk or Indexes. So is it better to create non-clustered index to remove table sca.
Create Index ind_Item_Name on Item(I_Name); Create Index ind_Item_BC on Item(I_BC); Create Index ind_Item_Company on Item(I_Company);
It is populated with 50 000 records. Searching on indexed columns is fast, but I've run into the following problem: I need to get all distinct companies in the table. I've tried with these two queries, but they both are very slow!
1. "select I_Company from item group by I_Company " - This one takes 19 seconds
2. "select distinct(I_Company) from item" -This one takes 29 secons
When I ran them through the SQL Management Studio and checked the performance plan, I saw that the second one doesn't use index at all ! So I focused on the first... The first one used index (it took it 15% of the time), but then it ran the "stream aggregate" which took 85% of the time ! Actully 15% of 19 seconds - about 2 seconds is pretty much enough for me. But it looks that aggregate function is run for nothing! So is it possible to force the query engine of the SSCE not to run it, since there is actually no aggregate functions in my select clause? According to SQL CE Books online: Group By
"Specifies the groups (equivalence classes) that output rows are to be placed in. If aggregate functions are included in the SELECT clause <select list>, the GROUP BY clause calculates a summary value for each group." It seems the aggregate is run every time, not only when there is an aggregate function.
I am doing performance tuning of SP/Query in Dev-Test environment.
I found that SQL Server caches plan between successive executions.
So if I test/execute SP 10 times, after 1st or 2nd execution, SQL server will pull-up plan-info from CACHE...Not from SQL SERVER Or Database...
Means i am not getting correct answer...
I found this 2 commands:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
But they say that executing above command might interfere/bother other people executing other query/sp on this server.
They also say that: Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance.
Part of query was using Dynamic-SQL executed with EXEC command.
I replaced that with SP_EXECUTESQL.
How can I start testing of each SP-run with Fresh/Blank CACHE ?
Using a 32-Bit SQL Server 2008 Express on my LOCAL PC. I downloaded the Advantage 8.1 OLE DB Provider and created a linked server to a REMOTE Advantage 8.1 database server. There is no data dictionary on the Advantage server.
Here is my linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'1xx.1xx.xx.1xx', @srvproduct=N'Advantage', @provider=N'Advantage OLE DB Provider', @datasrc=N'1xx.1xx.xx.1xxeccET', @provstr=N'servertype=ads_local_server;tabletype=ads_cdx;'--tabletype=’ADS_ADT’ (this test works too) EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'1xx.1xx.xx.1xx',@useself=N'False',@locallogin=Null,@rmtuser='adssys',@rmtpassword=Null
Testing the link succeeds with above. Using “ads_REMOTE_server” instead of “ads_local_server” and the test fails. Here is my problem, using the following queries will not work. Perhaps it’s a permissions issue? When I specify my local credentials for the remote server in the linked server it still does not work.
SELECT * FROM OPENQUERY([1xx.1xx.xx.1xx], 'SELECT * FROM ActType')
OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx" returned message "Error 7200: AQE Error: State = HY000; NativeError = 5004; [Extended Systems][Advantage SQL][ASA] Error 5004: Either ACE could not find the specified file, or you do not have sufficient rights to access the file. Table name: ActType SELECT * FROM ActType". Msg 7321, Level 16, State 2, Line 2
An error occurred while preparing the query "SELECT * FROM ActType" for execution against OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx".
I am having 'INSERT STATEMENT CONFLICTED' error since I try to configure sql server 2005 Full Text Search for my database. Before everything was going fine but after doing some changings I mess up my all DB's. None of my database is working. The error only occur while inserting the records using web form. But I can insert directly from Management Studio interface. here is part of my error:
ERROR:
System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Dept_LegalLaw_Dept_LegalMinistries1". The conflict occurred in database "LegalDB", table "dbo.Dept_LegalMinistries", column 'RegID'. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() ...............AND SO ON
INSERT QUERY:
INSERT INTO Dept_LegalLaw ( Agreement,Amendment,Attachment,Html,ID,IssueNo,LawDate,LawID,LawNo,Mistakes,Name,NameSearch,Pages,RegID,Scanned,SerialNo,Path) VALUES ( '1',NULL,NULL,'1','C0667','2666','5/26/2004 12:00:00 AM','2','22',NULL,N'تعميم بشأن عطلة ذكرى المولد النبوي الشري?',N'مرسوم اميري رقم 29 لسنة 2001 بانشاء لجنة الاسكان والاعمار','1','RAC,',NULL,'5556','RCAB4505.htm') --RETURN THE NEW IDENTITY VALUE SELECT SCOPE_IDENTITY() MediaID
I really need urgent help. that how i can reverse this thing back.
Hi all!I need your help to realize algorithm for stored proc or trigger.tool: MS SQL server 2000, T-SQLTABLE:[unique_id] [mynumber] [week][unique_id] - bigint,primary key, identity auto-increnment[week] - int, 1-53, week number[mynumber] - int, 1 - 7, for every week, daily record one per day, upto 7 per weekso, for every week we have a mynumber from 1 to 7or nothing (if no records for that day),we can insert or delete mynubers in any order, at willEXAMPLE:week 1, mynumber 1,2,3 - so if we insert a new record, mynumber value= 4week 2, mynumber 1,2,3,5,7 - so next mynumber = 4QUESTION:How to use _only_ T-SQL find a missed numbers for particular week whenI'm insert a records?Thanks.Chapai
I am stuck at a very awkward place. I have created one package which uses an oracle view as its source for data transfer the problem is when i run the package through dtexec it works fine but when i try to schedule it I get the following error
Error: 2008-03-24 13:52:40.22 Code: 0xC0202009 Source: pk_BMR_FEED_oracle Connection manager "Conn_BMR" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.
Provider is unable to function until these components are installed.".
I am able to run the package outside the sql job and also connect to the oracle. I have oracle 9i client installed on the server and sql server is 2005.
hi,I was pulling up a report in SQL, and I wanted the records to be ordered by dates descending. However, I found this ordering was only fine enough to order records by dates (not hours or minutes) (within the same date, records were ordered so that the latest entered were at the bottom). I wonder if anyone else has encouted this problem before, or I am doing something wrong.Thanks very much.
I have created the view below, when I highlight the select statement in QA it works fine, but when I try to access the view I get back 0 records! ie: select * from Email_All
It believe its something to do with the last not in section: EmailsAddresses.Email not in ( if I comment it out everything works fine!
does not make any sense...anyone have any clues?
they are all views except the EmailSubscriptions table.
------------------------------------------- SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
ALTER VIEW [dbo].[Email_All] AS
select * from ( --SHELBY select * from hcf..HM_EmailAddressShelby union all --KMS select * from hcf..HM_EmailAddressKmai union all --EMAIL SUBSCRIPTIONS select * from hcf..HM_EmailAddressWeb where id in (select distinct id from hcf.dbo.EmailSubscriptions where List = 'PeriodicEmailsFromGreg' and status = 1) ) as EmailsAddresses where ltrim(rtrim(isnull(EmailsAddresses.Email,''))) <> '' and EmailsAddresses.Email not in ( select Email from hcf..ShelbyIdEmailSkip --OFF list SHELBY union select Email from hcf..KmaiIdEmailSkip --OFF list KMAI union select Email from hcf..EmailSubscriptions where List = 'PeriodicEmailsFromGreg' and status = 0 --OFF list PERIODIC FROM GREG )
GO
SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO
I have a script that runs every night to pull some data from Delphi. For some strange reason the past month it has been failing on the inital running of the script but when I close off all of the error boxes and hit the script again it runs just fine.... Please find enclosed the script . Can anyone tell me whe it is wrong... thanks in advance
I created a couple of stored procedures. One of them (let's call it SP1) dumps information into a table and then calls another stored procedure (SP2) to put the info in a temp table in crosstab format. SP1 then displays the info from the temp table.
When I execute SP1 from Query Editor, everything works perfectly. No errors are returned and my data is displayed just as expected.
When I try to execute SP1 from MS Access's pass-through query, I get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ','.(#102)[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'A'(#102)
I've found that the error occurs when the SP2 is called from within SP1. If it's working fine in Query Editor, shouldn't it work from MS Access? Any insights?
Herre is my connection string:<add name="PageEngine" connectionString="Data Source=MyServerSQLEXPRESS;Initial Catalog=PageEngine;Integrated Security=True;User ID=MyID;Password=MyPwd;" providerName="System.Data.SqlClient"/>When I run the app using WDE (Web Developer Express), Everything works fine. I can access and diplay data from the database with no problems. However, when I try to run it from a browser on the local machine or a remote machine it fails to create a datareader. I am using the Database factory from Enterprise Library. IDataReader dr = pe.DataReaderFromStoredProcedure("pe_GetApplicationInformation", null, "PageEngine");if (dr.Read() != false) (* this is the line it fails on every time){_ApplicationName = dr["ApplicationName"].ToString();_Version = dr["Version"].ToString();_Copyright = dr["Copyright"].ToString();_Owner = dr["Owner"].ToString();}dr.Close(); Here is the "DataReaderFromStoredProcedure" methodpublic IDataReader DataReaderFromStoredProcedure(string spName, peParameters parameters, string connection){Database db;if (connection != null)db = DatabaseFactory.CreateDatabase(connection);elsedb = DatabaseFactory.CreateDatabase();DbCommand dbCommand = db.GetStoredProcCommand(spName);dbCommand.CommandType = CommandType.StoredProcedure;Here is the error message I get:Object reference not set to an instance of an object. 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.NullReferenceException: Object reference not set to an instance of an object.Source Error: Line 53: IDataReader dr = pe.DataReaderFromStoredProcedure("pe_GetApplicationInformation", null, "PageEngine"); Line 54: Line 55: if (dr.Read() != false) Line 56: { Line 57: _ApplicationName = dr["ApplicationName"].ToString();Source File: c:Public FoldersITWebSite ProjectsScotts_SandboxPageEngineWorkingApp_CodeBusiness LayerApplicationInformation.cs Line: 55 Stack Trace: [NullReferenceException: Object reference not set to an instance of an object.] PageEngine.ApplicationInformation..ctor(Page pg) in c:Public FoldersITWebSite ProjectsScotts_SandboxPageEngineWorkingApp_CodeBusiness LayerApplicationInformation.cs:55 login.GetTitle() in C:Public FoldersITWebSite ProjectsScotts_SandboxPageEngineWorkinglogin.aspx.vb:45 login.Page_Load(Object sender, EventArgs e) in C:Public FoldersITWebSite ProjectsScotts_SandboxPageEngineWorkinglogin.aspx.vb:13 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061 Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42 I have tried so many things found on the forums, I don't know what to do anymore![:'(]
I have an application that connects to an externally hosted SQL server. The first time i go to the web page, the application times out. I can then immediately refresh and everything works. After a period of about 20-30 minutes of inactivity, the issue repeats itself. This is very hard to trouble shoot. Anyone know what to do?
