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.
View 2 RepliesI have a table with clustered index on that. I have only 5 columns in that table. Execution plan is showing that Index scan occurred. What are the cause of the Index scan how can we change that to index seek?
I am giving that kind of similar query below
SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= '@salesId' and Product = 'Clothes '
How can I improve performance of my search if I am looking in a table with more than ten million rows with a "like query"?
Does putting an index mean only telling the computer to start in a particular order?
if I index all the coloums does my search get faster ?
how can I decide on an indexing strategy?
I am using SQL Server 2012 Express.
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 ?
Hi,
This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).
I'm looking at these new databases and I'm seeing code similar to this all over the place:
if not exists (select 1 from dbo.t1 where f1 = @p1)
begin
select @errno = @errno | 1
end
There's a unique clustered in dex on t1.f1.
The execution plan shows this for this statement:
FROM TABLE
dbo.t1
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
It's not using my index!!!!!
It seems to be the case with EXISTS statements. Can anybody confirm?
I also hinted to use the index but it still didn't use it.
If the existence check really doesn't use the index, what's a good code alternative to this check?
I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.
SET ROWCOUNT 1
SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01)
WHERE f1 = @p1
)
SET ROWCOUNT 0
Appreciate your help.
Hi
Is there any good books for Query Tuning and Stored procedure Tuning
Thanks
Hey,
what is the difference between Table Scan und Index Scan?
I find no difitions in the internet
Finchen
Hi,
I want to know wht is a
TABLE SCAN
INDEX SCAN
INDEX SEEKand When they are used, Wht is the difference between all these.????
I noticed today a session that was executing a FULL SCAN update as follows:
UPDATE STATISTICS [XXXX].[XXXX].[XXXX] [_WA_Sys_00000009_318D45CA] WITH FULLSCAN
When I checked the sys.dm_exec_query_memory_grants DMV for the session I could see the following values:
requested_memory_kb granted_memory_kb used_memory_kb max_used_memory_kb
145,705,216 145,705,216 139,977,336 139,980,408
When I checked the Properties of the Statistic I can see it is on a varchar(3) field when there are only 3 different values in there - all char(1)
The total size of the data in the table according to the Disk Usage By Top Table Report is 199,680,712KB
So my question is this...
For the UPDATE STATS on this one column with FULL SCAN, does SQL Server read the entire table into the Buffer Pool. If so then if the table had 199,680,712KB of data then why did the session request 145,705,216KB.
Or does SQL Server just read the column and ClusteredIndex Key into the Buffer Pool?
Can anyone tell me how to get rid of the Table Scan(1 million rows)being performed on the
The last line, option (loop loin) stopped table scanning the B.ss_manifest and started using the index, I'd like both tables to use the index.
This is the argument I get from execution plan under the table scan.
Object ((D4000).(dbo).(shipstop)as (A))
update drivers set dr_miles_run = case when D1.miles > 0 then d1.miles else 0 end
from (select mf_dr_nbr, sum( case when A.ss_end_dt < '05/17/00' then
( cast((datediff(day, '05/17/00' , B.ss_end_dt ) + 1) as float) /
cast( (datediff(day, A.ss_end_dt , B.ss_end_dt ) + 1) as float) * mf_ld_miles)
else mf_ld_miles end) as miles
from manifest, shipstop A, shipstop B
where mf_manifest_nbr = A.ss_manifest_nbr
and mf_manifest_nbr = B.ss_manifest_nbr
and A.ss_stop_type in ('OR','SA')
and B.ss_stop_type in ('DT','RD')
and ((A.ss_end_dt >= '05/17/00 00:00' and A.ss_end_dt < '05/24/00 00:00')
OR ((B.ss_end_dt >= '05/17/00 00:00' and B.ss_end_dt < '05/24/00 00:00'))) and mf_status > 3 group by mf_dr_nbr ) as D1
where Drivers.dr_driver_nbr = D1.mf_dr_nbr
option (loop join)
Thanks for any ideas!
Does SQL Server allow a table scan to be used when querying a table that has a clustered-index? If yes, could someone please show me the syntax? I have tried with (index(0)) but this appararently means a clustered index scan when there is a clustered index on the table to be queried.
Or does clustered index scan mean the same thing as a table scan when the table has a clustered index? Confused.
Thanks
Friends,
I am using this query in a table with 29,000 records having non-clustered index on emp_id
select * from employee
where emp_id between 100 and 1000
and gender = 'M'
order by emp_id
execution plan shows a table scan in table employee. Any reason?
select * from employee with (index = emp_id)
where emp_id between 100 and 1000
order by emp_id
ofcourse this uses index seek
regards,
Priw
I am working on tuning of sp. They wrote several left outer join. Is there any option to avoid them.
View 3 Replies View RelatedHi Everybody,
One of my friend asked me "How do we reduce the query logical, scan reads
in SQL Server?".
I really don't know, how to answer him.
Can anybody explain me regarding this.
thanks,
Srini
Hi I'm issuing a SELECT on a field with the SUM on SQL Server 7. I have an index on the field in the WHERE clause but upon analysis, the Query Optimizer always uses a Full Table Scan. Can anyone explain why and is there a way to use the index.
HEre's the structure:
SELECT SUM(colA)
FROM TABLE tblB
GROUP BY colC
An index exists on column colC.
Thanks
Hi guys-n-gals...
I have a table that contains the following:
PortfolioID(int), EndDate(smalldatetime), Begdate(smalldatetime), WklyCloseIndex (float)
It has a primary key which is PortfolioID/BegDate/EndDate
I need to create a table that summarizes, by date range, the weeklycloseindex of several portfolios, like this:
BegDate EndDate Portfolio2 Portfolio67 Portfolio11 Portfolio90
05/28/2004 06/04/2004 xx.xx xx.xx xx.xx xx.xx
05/21/2004 05/28/2004 xx.xx xx.xx xx.xx xx.xx
So I wrote a function...
This function results in a table scan *GASP!!!!* (at least that's what the 'splain plan tells me when I run it in SQL Analyzer). Before I made it into the function, when I was testing the code in SQL Analyzer, it resulted NOT in a table scan, but rather a series of nested loops (the joins) and clustered index seeks...resulting in about 1/3 the total cost of the function.
I suspected originally that it was the TOP/ORDER BY that the function insisted upon, but even if I remove those, still get a table scan.
Wassup? Why does the function turn my cool lil' self-join into a table scan? Whut am I missing? Any thoughts? Disgusted Derisions? Hurled Insults? Bring it on!!! (please! ;) )
My predecessor did this in a similar project using a separate cursor for each portfolio by date, then looped through the dates, pulling in the per-portfolio index value and building the output table. I would rather avoid the cursors if I can.
I have the following scenario (with 2K5 Express)
I have a table with 160.000 records.
I have to retrieve 10.000 - 40.000 records by their ids (<3seconds would be sufficent)
I first used single requests, then one single command as batch (simply joined the single commands into one string).
But that was very slow (30 seconds if cached). so I created one big statement
select myfields
from mytable where id in(1,2,..,35000)
if everything is cached the speed is fine (<1second), but if I retrieve the data for the first time
it takes 15-30 seconds, that's a bit too slowish.
the total database size is 100MB - so a file scan should be faster, I thought at least
so HERE is the problem why I post this
to force the table scan I used
Select myFields From mytable With (Index(0) ...
that took > 3 minutes
I tested the raw IO-time, that was 2,5-3 seconds with the db-file
has SQL Server a problem with the 35.000 items in the condition?
(If it loopes 35.000 x 160.000 times instead of using a hash for the items that would explain the slow speed)
or another reason:
is table scanning always much slower then the raw io operations?
the id-index is not grouped and ( I really don't know why) not marked as primary key,
but that shouldn't have any impact on a file-scan, I guess.
Has anyone faced (and solved) a similiar problem?
I have following query to delete the data from fact history table based on fact table. logid, level and post_date uniquely identify the rows on both fact and history table. I want to create indexes on the joined columns.I tried with clustered index (logid, level and post_date) it gives clustered index scan. I also tried with non clustered indexes on each column (logid, level and post_date) but still getting table scan.
Do you have any suggestion on which columns should I create proper indexes to avoid table or index scan? There are about 6 million rows on each table.
DELETE xbar_fact_history
FROM xbar_fact_history AS a
INNER JOIN xbar_fact AS b
ON a.logid = b.logid
AND a.level = b.level
AND a.post_date = b.post_date
AND a.check_CheckSum <> BINARY_CHECKSUM(b.out_mins,b.nor_hrs,b.pdi_call)
______________________________________________________________
Thanks
Sanjeev
Hello,
I have a stored procedure that's running a little slower than I would like. I've executed the stored proc in QA and looked at the execution plan and it looks like the problem is in a trigger on one of the updated tables. The update on this table is affecting one row (I've specified the entire unique primary key, so I know this to be the case). Within my trigger there is some code to save an audit trail of the data. One of these statements does an update of the history table based on the inserted and deleted tables. For some reason this is taking 11.89% of the batch cost (MUCH more than any other statement) and within this statement 50% of the cost is for a table scan on inserted and 50% is for a table scan on deleted. These pseudo-tables should only contain one record each though.
Any ideas why this would be causing such a problem? I've included a simplified version of the update below. The "or" statements actually continue for all columns in the table. The same trigger template is used for all tables in the database and none of the others seem to exhibit this behavior as far as I can tell.
Thanks for any help!
-Tom.
UPDATE H_MyTable
SET HIST_END_DT = @tran_date
FROM H_MyTable his
INNER JOIN deleted del ON (his.PrimaryKey1 = del.PrimaryKey1) and
(his.PrimaryKey2 = del.PrimaryKey2)
INNER JOIN inserted ins ON (his.PrimaryKey1 = ins.PrimaryKey1) and
(his.PrimaryKey2 = ins.PrimaryKey2)
WHERE (his.HIST_END_DT is null)
and ((IsNull(del.PrimaryKey1, -918273645) <>
IsNull(ins.PrimaryKey1, -918273645)) or
(IsNull(del.PrimaryKey2, -918273645) <>
IsNull(ins.PrimaryKey2, -918273645)) or
(IsNull(del.Column3, -918273645) <>
IsNull(ins.Column3, -918273645)))
Anyone see a way to trick the optimizer into not scanning all tables involved in the view?
-- create two test tables
create table dbo.test1
(testID int, TestName varchar(10))
create table dbo.test2
(testID int, TestName varchar(10))
-- populate
declare @i int
set @i = 1000
while @i > 0
begin
insert into dbo.test1
select @i, '1.' + cast(@i as varchar(5))
set @i = @i - 1
end
insert into dbo.test2
select 1, '2.1' union all
select 2, '2.2'
go
-- create view
create view dbo.vw_Test
as
select1 as QueryID,
TestName
fromdbo.Test1
union all
select2 as QueryID,
TestName
fromdbo.Test2;
go
-- this works as i want, only scans table dbo.Test2
select *
fromdbo.vw_Test
whereQueryId = 2
-- joining to a table triggers scan of both tables in view:
declare @table table (QueryID int)
insert into @table
select 2;
selectvt.TestName
fromdbo.vw_Test vt
join@table t on
vt.QueryID = t.QueryID
Using the showplan I can see why the optimizer ends up scanning all tables, but maybe there is a way to force it to use the QueryID param evaluation earlier in the filtering.
Nathan Skerl
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
[code]...
Hi,
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)))
Thanks
Prashant
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')
|--Nested Loops(Inner Join)
|--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]))
| | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_Profile].[InventoryStatusID]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]))
| | | | |--Clustered Index Scan(OBJECT:([LW_Winthrop].[dbo].[Inventory_PTMS].[PK_IInvPTMS]))
| | | | |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[Inventory_Profile].[PK_IInventory_InvID]), SEEK:([LW_Winthrop].[dbo].[Inventory_Profile].[InventoryID]=[LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[PK_cdInvStatus_Locale]), SEEK:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusID]=[LW_Winthrop].[dbo].[Inventory_Profile].[InventoryStatusID]), WHERE:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Donated' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Scrap' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Sold' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Write Off') ORDERED FORWARD)
| | |--Index Seek(OBJECT:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[UK_ITaxDepInfo]), SEEK:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryID]=[LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[PK_ITaxDepInfo]), SEEK:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]=[LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]) LOOKUP ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[PK_cdInvStatus_Locale]))
I've been having some trouble getting a single-column "varchar(5)" field to reliably use a table seek instead of a table scan. The production table in this case contains 25 million rows. As impressive as it is to scan 25 million rows in 35 seconds, the query should run much faster.
Here's a partial table description:
CREATE TABLE [dbo].[Summaries_MO]
(
[SummaryId] [int] IDENTITY(1,1) NOT NULL,
[zipcode] [char](5) COLLATE Latin1_General_100_BIN2 NOT NULL,
[Golf] [bit] NULL,
[Homeowner] [bit] NULL,
[Code] .....
Typically, this table is accessed with a query that includes:
SELECT ...
FROM SummaryTable
WHERE ixZIP IN (SELECT ZipCode FROM @ZipCodesForMO)
This query insists on using a table scan. I've tried WITH (FORCESEEK) for example, but that just makes the query fail.
As I've investigated this issue I also tried:
SELECT * FROM Summaries WHERE ZipCode IN ('xxxxx', 'xxxxx', 'xxxxx')
When I run this query with 64 or fewer (actual, valid) ZIP codes, the query uses a table seek.But when I give it 65 or more ZIP codes it uses a table scan.
To summarize, the production query always uses a table scan, and when I specify 65 or more ZIP codes the query also uses a table scan. I'm wondering if the data type of the indexed column (Latin1_General_100_BIN2) is somehow the problem. I'll likely try converting the ZIP codes to an integer to see what happens.
hi
i have table i use it for update insert
and the users use this table from a grid on the web
and i need to prevent from white space in the fields in table
so how to
create TRIGGER remove white space from a fields in table scan and fix it ?
Code Snippet
SELECT TRIM(fieldname)
, LTRIM(fieldname)
, RTRIM(fieldname)
, LTRIM(RTRIM(fieldname))
FROM tablename
Code Snippet
WHERE (LTRIM(RTRIM(fieldname)) = 'Approve')
Code Snippet
replace(@text,' ','')
create TRIGGER on update insert and not to damage the text in the all fields
TNX
Hello
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)
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 lookfor matchin a triangle. Like this member A -> B->CA give his appartment to B. B gives his appartment to C and finallyC gives his appartment to ASoo my query looks for matching parameters like rooms, location, sizeandsoo on..I have one table for existing appartments and one for "whantedappartments"and 1 table called "intresse" where members can store "yes" or "no" ifthey 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 fewseconds instead of 20-30 secThanks MSELECTF.medlemsNr as medlemsNr, F.lfId AS lfId, F.ort AS ort, F.gatuadressAS gatuadress, F.gatuNr AS gatuNr, F.rum AS rum,F.storlek ASstorlek,F.hyra AS hyra, count(F.medlemsNr) As hitsFROMmedlem08 A, medlem08 B, medlem08 C, lagenhetF08 D,lagenhetO08 E, lagenhetF08 F, lagenhetO08 G, lagenhetF08 H,lagenhetO08 IWHERED.rum >= I.rumMin AND D.rum <= I.rumMax ANDD.storlek >= I.storlekMin AND D.storlek <= I.storlekMax ANDI.hyraMax = 0" & " OR D.hyra <= I.hyraMax) ANDI.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) ANDD.postNr IN (select postNr from ONSKEMAL08 where loId=I.loId) ANDF.medlemsNr Not IN (select medlemsNr2 from INTRESSE08 wheremedlemsNr1=A.medlemsNr) ANDH.rum >= G.rumMin AND H.rum <= G.rumMax ANDH.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) ANDH.postNr IN (select postNr from ONSKEMAL08 where loId=G.loId) ANDF.rum >= E.rumMin AND F.rum <= E.rumMax ANDF.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) ANDF.postNr IN (select postNr from ONSKEMAL08 where loId=E.loId) ANDA.medlemsNr=D.medlemsNr AND A.medlemsNr=E.medlemsNr ANDB.medlemsNr<>A.medlemsNr AND C.medlemsNr<>A.medlemsNr ANDB.medlemsNr<>C.medlemsNr ANDB.sparr<>1 AND C.sparr<>1 ANDA.typ=11 AND A.medlemsNr=" & session("medlemsNr") & " ANDB.medlemsNr=F.medlemsNr AND B.medlemsNr=G.medlemsNr ANDB.typ=11 AND A.triangel=1 AND B.triangel=1 AND C.triangel=1 AND " &_C.medlemsNr=H.medlemsNr AND C.medlemsNr=I.medlemsNr ANDC.typ=11 group by F.lfId, F.medlemsNr,F.ort,F.gatuadress,F.gatuNr,F.rum,F.storlek,F.hyra
View 9 Replies View RelatedTo avoid locking/blocking, or in transaction scope, we are trying make a common practice of writing coide for update commands in our all SPs based on primary key columns in where clause. I have a following scenario...
UPDATE [dbo].[TL_CST_Locker_Issuance] SET
[isActive] = 0
WHERE
LockerIssuanceId IN (SELECT LockerIssuanceId
[Code] ...
What is the better approach and should be followed to avoid locks and gain performance or best approach.
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?
Thanks, Vinnie
I have a the following query which takes long time
DECLARE @ACCOUNTS TABLE(ACCOUNT_ID INT)
INSERT INTO @ACCOUNTS
SELECT ACCOUNT_ID FROM ACCOUNT
WHERE A_DESCR in ('AA', 'BB', 'CC', 'DD', 'EE', 'FF', 'GG')
I used the following key words: sql server query tuning in operator
Hello all.
I have the following table
Create Table Item(
I_AssetCode NVarChar(40) Primary Key NOT NULL,
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20)
);
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.
Is this a bug?
Thanks in advance,
TipoMan
I have order history table that tracks who worked on a order
ID. ColA. ColB. ColC
1. Process 1234
2. Work. 7666
3. Return. 6789
4. Work. Null Role1
5. Return. 6538
I want a query to return recent colB or ColC where colA or colB are not null. In this case row 4
I have order history table that tracks who worked on a order
Another example
ID. ColA. ColB. ColC
1. Work. 1234
2. Process. 7666
3. Return. 6789
I want a query to return most recent colB or ColC where colB or ColC are not null. In this case row 2