Avoid Index Scan With LIKE And A Variable
Jul 23, 2005
Hi,
Here's my problem: I want to write a stored procedure that returns all
records from a table that have a certain column starting with given
text. I however find that using LIKE and a variable always causes an
index scan... which is causing performance issues. My table has about
3.5M records.
Below is a test. In query analyser if I look at the execution plan for
the following it will come up as in index scan. However, if i just
hard-code the text it all works fine (index seek).
How can I do this with reasonable speed???
Thanks Greg
DECLARE @find varchar(50)
SET @find = 'start'
SELECT TOP 100
*
FROM Test
WHERE
Col1 LIKE @find + '%'
--Col1 LIKE 'start%'
View 1 Replies
ADVERTISEMENT
Sep 29, 2006
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
View 5 Replies
View Related
Oct 4, 2007
Hey,
what is the difference between Table Scan und Index Scan?
I find no difitions in the internet
Finchen
View 5 Replies
View Related
Sep 21, 2007
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.????
View 5 Replies
View Related
Jan 29, 2006
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?
View 7 Replies
View Related
Oct 20, 2006
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
thank you in advance
View 3 Replies
View Related
May 26, 2008
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
View 6 Replies
View Related
Mar 1, 2004
I have a really strange problem.
I execute this query:
declare @cid int
set @cid = 2003227
select * from sales s, product p where p.product_Id = s.product_Id and customer_id = @cid
select * from sales s, product p where p.product_Id = s.product_Id and customer_id = @cid or @cid = 0
3 Million rows in sales, 120000 in product.
The first does and index seek, the second an index scan.
The execution plan reports that the scan takes 99.87% of the cost, and the seek takes 0.13%
This problem obviously gets worse the bigger the dataset / query /etc.
The reason I query this, is because it never used to take this long to do index scans. Is there something i can change, something i can fix?
Any help would be appreciated.
Josh
View 2 Replies
View Related
Nov 14, 2006
the query:
SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WHERE a.AssociationGuid IN (
SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada
WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
takes 30-60 seconds to run on my machine, due to a clustered index scan on our an index on asset [about half a million rows]. For this particular association less than 50 rows are returned.
expanding the inner select into a list of guids the query runs instantly:
SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WHERE a.AssociationGuid IN (
'0F9C1654-9FAC-45FC-9997-5EBDAD21A4B4',
'52C616C0-C4C5-45F4-B691-7FA83462CA34',
'C95A6669-D6D1-460A-BC2F-C0F6756A234D')
It runs instantly because of doing a clustered index seek [on the same index as the previous query] instead of a scan. The index in question IX_Asset_AssociationGuid is a nonclustered index on Asset.AssociationGuid.
The tables involved:
Asset, represents an asset. Primary key is AssetGuid, there is an index/FK on Asset.AssociationGuid. The asset table has 28 columns or so...
Association, kind of like a place, associations exist in a tree where one association can contain any number of child associations. Each association has a ParentAssociationGuid pointing to its parent. Only leaf associations contain assets.
AssociationDataAssociation, a table consisting of two columns, AssociationGuid, DataAssociationGuid. This is a table used to quickly find leaf associations [DataAssociationGuid] beneath a particular association [AssociationGuid]. In the above case the inner select () returns 3 rows.
I'd include .sqlplan files or screenshots, but I don't see a way to attach them.
I understand I can specify to use the index manually [and this also runs instantly], but for such a simple query it is peculiar it is necesscary. This is the query with the index specified manually:
SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WITH (INDEX (IX_Asset_AssociationGuid)) WHERE
a.AssociationGuid IN (
SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada
WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
To repeat/clarify my question, why might this not be doing a clustered index seek with the first query?
View 15 Replies
View Related
Jan 16, 2002
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
View 1 Replies
View Related
Nov 4, 2002
I have created a nolock view off a table to prevent locks. I have users coming in through MS Access that have switched their queries to run against the views. Now we are noticing that queries that used to run as a clustered index seek against the table are running as a clustered index scan against the table and performance in the queries has dropped.
Is there any way that the same query that hits the view instead of the table can be made to run faster or at least use the index seek?
Thanks,
Steve
View 4 Replies
View Related
Apr 8, 2008
I am running a SELECT on a table. This READ operation ends up going through Clustered Index Scan. I want to know whether Clustered Index Scan , blocks other concurrent transactions trying to INSERT into this table? Does Clustered Index Scan locks the entire clustered index?
View 3 Replies
View Related
Mar 28, 2014
I 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 '
View 7 Replies
View Related
Oct 26, 2006
Greetings,
I have two tables:
CustomerOrder
----
ID
CustomerID
StatusID
CustomerOrderDetail
----
ID
Order_ID
StockID
Quantity
CustomerOrderDetail table has clustered unique index for ID and non-clustered for Order_ID
SQL Server 2005 is using table scan for CustomerOrderDetail table When I user the following query:
select
cod.*
from CustomerOrder co
inner join CustomerOrderDetail cod ON cod.Order_ID = co.ID
where
co.StatusID = 8 -- Pending
Both of the tables are pretty big, detail table has more than million records, so scanning the table is a bad idea.
When I specify hint to use index then sql seeks, but how do I make SQL server to use index automatically? I don't want to use hints in my queries.
Thanks!
View 4 Replies
View Related
Sep 20, 2006
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.
View 3 Replies
View Related
May 31, 2001
I am creating a table variable (@tblBin) to temporarily store a set of data. Later in my sproc, I am doing a JOIN from @tblBin to a persistent table. In order to improve performance, I was thinking of adding an index to the columns of the @tblBin (indexes already exist on the persistent table). Using standard CREATE INDEX syntax(*), I am getting a compile error. Can this be done?
(*)CREATE NONCLUSTERED INDEX IX_tblBin_shortname ON @tblBin(shortname)
TIA ...
Regards,
~DLDay
View 1 Replies
View Related
Jul 20, 2005
Hi,I've got 2 table variables inside of an SQL 2000 function:@tmpBigList(BItemID, BRank)@tmpSmallList (ItemID, Rank)The following UPDATE statement can run for a long time if @TmpTable1has 500 rows and @TmpTable2 has 35,000 rows.UDPATE @tmpBigListSET BRank = t.RankFROM @tmpBigListJOIN @tmpSmallList t on t.ItemID = BItemIDLooking at the Query Plan, you see that the INNER JOIN Of @tmpBigListto @TmpSmallList results in 500 * 35,000 = 17,500,000 rows beingreturned from @TmpSmallList. That takes a long time.An index would help, but it appears that you can't add an index to atable variable.Changing to a temp table does not work since it's in a function.Thanks,Joe Landes
View 1 Replies
View Related
Sep 26, 2007
Hi ,
I would like to create index for a table and that index name must be random generated.
How to do this?
declare @value varchar(50)
set @value = rand()
set @value = @value + 'index-name'
create index @value on tablename(variables)
View 1 Replies
View Related
Oct 5, 2015
Why the index value is used in variable mapping while using for each loop container. In one of ssis package I saw the index value as 2. What 2 indicates in index?
View 3 Replies
View Related
Jul 25, 2006
Hi:
I am trying to loop through 4 files in a folder and read the names of those files through the For each loop container task. I have 4 readme files (readme1.txt thru readme4.txt) in a folder called C:SSIS.
I have added a for each Loop container and a script task to my package. In the Variable Mapping page I have named the Variable and configured the index to be 0. The problem is when I execute the package the file name that is read is always readme1.txt. I want all the file names to be read under the folder ( in other words configure the index to be 0 thru 4). How do I configure the index so that it can read all the files?.
The following is the code I have in my script task:
Public Sub Main()
Dim variables As Variables
If Dts.Variables.Contains("FileName") = True Then
Dts.VariableDispenser.LockOneforRead("FileName", variables)
End if
Msgbox ("Retrieved the File " & CStr(variables("FileName").Value))
Dts.TaskResult=Dts.Results.Success
End Sub
When I execute the package with the above code, I always see a message box popping up and saying Retrived File Readme1.txt.
I want the msgbox to pop up and say
Retrieved Readme1.txt
Retrieved Readme2.txt
Retrieved Readme3.txt and so forth...
Any help on achieving this is appreciated.
Thanks
AK
View 1 Replies
View Related
Feb 26, 2008
Hi all,
my stored procedure have one table variable (@t_Replenishment_Rpt).I want to create an Index on this table variable.please advise any of them in this loop...
below is my table variable and I need to create 3 indexes on this...
DECLARE @t_Replenishment_Rpt TABLE
(
Item_Nbr varchar(25) NULL,
Item_Desc varchar(255) NULL,
Trx_Date datetime NULL,
Balance int NULL,
Trx_Type char(10) NULL,
Issue_Type char(10) NULL,
Location char(25) NULL,
Min_Stock int NULL,
Order_Qty int NULL,
Unit char(10) NULL,
Issue_Qty int NULL,
Vendor varchar(10) NULL,
WO_Nbr varchar(10) NULL,
Lead_Time int NULL,
PO_Nbr char(10) NULL,
PO_Status char(10) NULL,
Currency char(10) NULL,
Last_Cost money NULL,
Dept_No varchar(20) NULL,
MSDSNbr varchar(10) NULL,
VendorName varchar(50) NULL,
Reviewed varchar(20) NULL
)
I tryed all below senarios...it is giving error...
--Indexing the @t_Replenishment_Rpt table on the column Names Item Number, Vender , Department Number
--EXEC sp_executesql(CREATE UNIQUE CLUSTERED INDEX Replenishment_index ON @t_Replenishment_Rpt (Item_Nbr))
--CREATE UNIQUE CLUSTERED INDEX Idx1 ON @t_Replenishment_Rpt.Item_Nbr
INDEX_COL ( '@t_Replenishment_Rpt' , ind_Replenishment_id , Item_Nbr )
--EXEC sp_executesql('SELECT INDEXPROPERTY('+ '@t_Replenishment_Rpt' + ', ' + 'Item_Nbr' + ',' + 'IsPadIndex' + ')')
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Vendor','IsPadIndex'))
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Dept_No','IsPadIndex'))
View 3 Replies
View Related
Oct 8, 2007
I write a work about database, and I want to write the diffrence between Table scan and Index scan. When is the index scan effectient? Use the database a Table scan when there is an index on the table or always a index scan when the index is nonselective? Can be a Table Scan effecient than a index scan?
View 1 Replies
View Related
Aug 14, 2001
I am having problems w/ the indexes on a particular table. Currently the scan density if 25%. I have ran the dbcc dbreindex and the scan density does not improve. I have manually dropped and recreated all of the indexes on the table (there are only two) and still no help.
I have a clustered Primary Key index on my part_number and project_number fields and another index on the project_number field. If I drop the second index and only have my primary key index, it still doesn't help. The fill factor was originally set to 90% and I changed this to 30%, 20%, 10%, 0% and the best scan density I could acheive was 50%.
The data in this table is not updated that often, on average about 15 records a day are updated, and there are only 107 records. This is a small table, but there is a possibility for it to grow rapidly and I want to be prepared now.
Does anyone have any ideas or suggestions? I've tried everything I can think of with no luck.
TIA,
Chris
View 7 Replies
View Related
May 26, 2000
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!
View 3 Replies
View Related
Aug 21, 2006
Hi,
I have a simple temp table which looks like this:
CREATE TABLE #t7e07c01fa80143ff84cb14a2307809f7
(
[AUTOID_TEMP] [int] IDENTITY (1, 1) NOT NULL,
...
)
ON [PRIMARY]
ALTER TABLE #t7e07c01fa80143ff84cb14a2307809f7 ADD
CONSTRAINT [PK_t7e07c01fa80143ff84cb14a2307809f7] PRIMARY KEY NONCLUSTERED
(
[AUTOID_TEMP]
)
ON [PRIMARY]
Insert Into #t7e07c01fa80143ff84cb14a2307809f7
Selet columns... from t....
-- get total records
Declare @TotalCount as int
select @TotalCount = count(AUTOID_TEMP) from #t7e07c01fa80143ff84cb14a2307809f7
Now the above last statement does a index scan. I am new to indexing/tuning and was wondering if its normal - if so why and can I somehow enhance this?
Thanks
View 5 Replies
View Related
Nov 1, 2007
Hey guys,
I have a procedure that runs everyday which takes the IIS log file from the previous day and imports it and calcs values. I would like to change this from taking the previous day to scanning all the file names in the folder and comparing them to a database to see if they have already been scanned before. I already have a table called DatesScanned which lists the filename and scandate.
IE:
D:Logsfile1.log
D:Logsfile2.log
D:Logsfile3.log
D:Logsfile4.log
The 4 file names will be scanned and compared to DatesScanned to see which has been imported already.
Can anyone point me to a resource or information on how I would get the directory listing of a folder so I could compare it to the DatesScanned table.
Thanks a bunch!
Jeff
View 5 Replies
View Related
Jul 23, 2005
Hi,Can someone please explain the following1. Meaning of scan count as reported when "statistics io" is turned onprior to running a query.2. In which situations could you have an identical database runningon two diferent servers , with identical database serverconfigurations, running identical queries, with identical query plan,report large discrepency in the scan count . This is one of thepossibilities we are looking at in terms of the reason why one serverruns the job in 12 hours and the other in 24 hours.Server 1--------Table 'TRANS_HISTORY'. Scan count 216, logical reads 897093, physicalreads 44, read-ahead reads 900599.SQL Server Execution Times:CPU time = 27766 ms, elapsed time = 46850 ms.UNIT_NUMBERACCOUNT_TYPETRANSACTION_TYPEServer 2--------Table 'TRANS_HISTORY'. Scan count 491, logical reads 952759, physicalreads 51, read-ahead reads 954414.SQL Server Execution Times:CPU time = 31563 ms, elapsed time = 145595 ms.UNIT_NUMBERACCOUNT_TYPETRANSACTION_TYPEI thank you in advance for your assistance.Puvendran
View 1 Replies
View Related
Jul 9, 2007
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
View 1 Replies
View Related
Nov 28, 2007
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
View 15 Replies
View Related
May 21, 2007
I do not understand it but by connection to my database seems to keep crapping out. I get the below error and my app appears to be unable to access the connection. Then a couple restarts later it tells me that my login under my Windows User failed . The database is there and besides maybe a couple records longer there isnt anything corrupt about the data, and in fact, if I restart the entire PC most times it will allow me to connect again. I can even still access the data tables through the C# editor. Any idea why this error is being thrown?
Thx
Code SnippetSystem.Data.SqlClient.SqlException was unhandled
Message="A system assertion check has failed. Check the SQL Server error log for details
The log scan number (35:460:2) passed to log scan in database 'F:\PDLOGSHEET\PDLOGSHEET\BIN\DEBUG\DATABASE1.MDF' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
An error occurred during recovery, preventing the database 'F:\PDLOGSHEET\PDLOGSHEET\BIN\DEBUG\DATABASE1.MDF' (database ID 26) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Cannot open user default database. Login failed.
Login failed for user 'GC\cbcottier'.
Location: logscan.cpp:1925Expression: UtilDbccIsInsideDbcc ()SPID: 51Process ID: 3640
Location: logscan.cpp:1925Expression: UtilDbccIsInsideDbcc ()SPID: 51Process ID: 3640"
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
Class=20
LineNumber=65536
Number=3624
Procedure=""
Server="\\.\pipe\E74940B1-0BD7-42\tsql\query"
State=1
StackTrace:
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at PDLogSheet.Database1DataSetTableAdapters.ReportTitlesTableAdapter.Fill(ReportTitlesDataTable dataTable) in F:PDLogSheetPDLogSheetDatabase1DataSet.Designer.cs:line 4362
at PDLogSheet.frmLogSheet.frmLogSheet_Load(Object sender, EventArgs e) in F:PDLogSheetPDLogSheetfrmLogSheet.cs:line 42
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
at System.Windows.Forms.Control.set_Visible(Boolean value)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at PDLogSheet.Program.Main() in F:PDLogSheetPDLogSheetProgram.cs:line 17
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
View 5 Replies
View Related
Oct 29, 2007
Hi All,
When an end user view a report using report manager or a custom build web site, would query of the report scan the tables in original data base? In this case it is a OLTP data base.
Thanks
View 4 Replies
View Related
Aug 27, 2001
Case
I run dbcc showcontig
Result
DBCC SHOWCONTIG scanning 'rpRateCode' table...
Table: 'rpRateCode' (213627854); index ID: 1, database ID: 17
TABLE level scan performed.
- Pages Scanned................................: 2
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2] !!!!!!!
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 3088.5
- Avg. Page Density (full).....................: 61.84%
Bellow is table structure
CREATE TABLE [dbo].[rpRateCode] (
[ID_RateCode] [ID] NOT NULL ,
[ID_ScheduleOfPresence] [ID] NULL ,
[ID_ScheduleOfBilling] [ID] NULL ,
[IsExtensible] [bit] NOT NULL ,
[IsFixedRate] [Boolean] NOT NULL ,
[ID_RateCodeFollow] [ID] NULL ,
[ID_RateDef] [ID] NULL ,
[ID_MarketSegment] [ID] NOT NULL ,
[ID_HskLevelPattern] [ID] NULL ,
[ComplimentaryType] [AType] NULL ,
[BillingType] [AType] NULL ,
[Discriminator] [Discriminator] NOT NULL ,
[PrintRateOnConfirm] [Boolean] NOT NULL ,
[PrintRateOnArrivalList] [Boolean] NOT NULL ,
[PrintRateOnRegCard] [Boolean] NOT NULL ,
[MinNights] [NbOfNights] NULL ,
[MaxNights] [NbOfNights] NULL ,
[IsLimited] [Boolean] NULL ,
[presencetype] [tinyint] NULL ,
[RateNote] [varchar] (60) NULL ,
[IsAgeCatDiffRoom] [Boolean] NULL ,
[ID_Currency] [ID] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[rpRateCode] WITH NOCHECK ADD
CONSTRAINT [PK__rpRateCode__2EBAFAC0] PRIMARY KEY CLUSTERED
(
[ID_RateCode]
) ON [PRIMARY]
GO
My question how to increase Scan Density for this table if CLUSTERED index exist?
View 1 Replies
View Related
Dec 16, 2005
Can someone explain why i can drop and recreate indexes on say 2 tables and they had a scan densitiy of say 15% and 10%.
After i recreate the indexes on both tables one comes back with a scan density of 100% and the other only 25%.
What would cause this table to only go to 25% and not 100% like i want it to be?
Thanks for your help :)
View 13 Replies
View Related
