Hi,I am making as SELECT query to fill a repeater, and I need to retrieve the index of each line of the query.ie, I want to get a dataset like :"0", "dataCol1", "dataCol2" for the first line"1", "dataCol1", "dataCol2" for the second line"2", "dataCol1", "dataCol2" for the third lineetc.Anyone knows if there is a sql statement that does it ?ThanksJohann
View 2 Replieshello friends
i have table1 and 200 coulumn of table1 :) i have 647.600 records. i entered my records to table1 with for step to code lines in one day :)
i select category1 category2 and category3 with select code but i have just one index.. it is productnumber and it is primarykey..So my select code lines is so slow.. it is 7-9 second.. how can i select in 0.1 second ? Should i create index for category1 and category2 and category3 ? But i dont know create index.. My select code lines is below.. Could you learn me and show me index for it ?? or Could you learn me and show me fast Select code lines and index or etc ??? Also my search code line have a dangerous releated to attaching table1 with hackers :)
cheersi send 3 value of treview1 node and childnode and child.childnode to below page.aspx :)
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
If Request("TextBox1") IsNot Nothing ThenTextBox1.Text = Request("TextBox1")
End If
If Request("TextBox2") IsNot Nothing ThenTextBox2.Text = Request("TextBox2")
End If
If Request("TextBox3") IsNot Nothing ThenTextBox3.Text = Request("TextBox3")
End If
End If
Dim searchword As String
If Request("TextBox3") = "" And Request("TextBox2") = "" Then
searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "'"
End If
If Request("TextBox3") = "" Then
searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "'"
End If
If Request("TextBox3") <> "" And Request("TextBox2") <> "" And Request("TextBox1") <> "" Then
searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "' and kategori2= '" & Request("TextBox3") & "'"
End If
SqlDataSource1.SelectCommand = searchword
End Sub
Hi All,
I 'm working to improve some sql performance.
One of the major syntax inside the SELECT statment is ..
WHERE FIELDA IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='A') AND
WHERE FIELDB IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='B') AND
WHERE FIELDC IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='C') AND
WHERE FIELDD IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='D') AND
WHERE FIELDE IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='E') AND
WHERE FIELDF IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='F')
(It's to compare the field content with some user input parameter inside a parameter table... )
I think properly is that the SELECT ... IN is causing much slowness in the sql statement. I have indexed FIELDA , FIELDB, FILEDC etc and those PARAVALUE and PARATYPE in the PARATABLE table. But perfromance is still slow and execution takes >20 seconds for 200000 rows of records.
Do any one know if still any chance to improvide the performance like this?
Much Thanks,
Andy
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?
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?
for MS SQL 2000
the following will work if I want to have UNIQUE Users.Name >>
INSERT INTO [Users] (Name)
SELECT Names FROM OtherUsers
where OtherUsers.Names not in (select Name from Users)
but if I have an UNIQUE INDEX on Users
CREATE UNIQUE INDEX [IX_Users] ON [Users] ([Name],[Category]) ON [PRIMARY]
how can I do it ?
INSERT INTO [Users] (Name, Category)
SELECT Names,Categories FROM OtherUsers
where OtherUsers.Names + OtherUsers.Categories not in (select Name, Category from Users) ?
how can I insert it wih an index on 2 or 3 columns ?
thank you for helping
I am new to SQL, especially use SQL in VC++ 6.0 framework.
I am told that creating INDEX on field(s) could speed up a query.
if I create a INDEX like the following
<code>
CREATE nonclustered INDEX IX_XYZ on TableA.field1
</code>
Should I use the INDEX name IX_XYZ in some way in the following SELECT statement. Or the following SELECT statement will be carried out automatically based on the INDEX IX_XYZ.
<code>
SELECT * FROM TableA WHERE field1 = xxx
</code>
Eliko writes "i would like to add a coloumn to a select result, so there will be another coloumn with indexed running numbers for each record.
how can i do it?
thank you
eliko"
HELP
Dmitri
I want to Select a value from a Column which is named "Index" - and I don't want to change the name naturally. Is there a way to get a value without mentioning the Column name itself, rather the column number? "Incorrect syntax near 'Index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax."
Occurs when I try to simply..
-> SELECT Index FROM Table WHERE Name like 'xyz' <-
I want to SELECT the result from table, but i want the result return in record entry order, instead of sort by index or ORDER BY certain field.
View 14 Replies View RelatedHello all,
how can I select one or more columns from a table by column-index and NOT by columnname?
e.g.:
SELECT tbl1.[1], tbl1.[2], tbl1.[3] FROM Orders AS tbl1
and NOT like this:
SELECT tbl1.OrderNo, tbl1.ProductNo, tbl1.Price FROM Orders AS tbl1
Is that possible in MS-SQL 2000?
Thanks a lot in advance
kind regards
Otto
Hello!
I would like when I compare query plans to be able to compare
2 queries where the 1. is the "normal" version and the 2. the version where I forced compiler
not to use a specific index (i don't want to force at table scan, so hint index(0) can't be used).
The only way I see how I could achieve something similar is to to drop the index and compare response time
before and after. But building index could be time consuming...
Is it possible?
select col42 from tbl;
select col42 from tbl with...
pressing CTRL+L
Greetings
Bjørn
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
MS SQL Server 2000 SP3
I'm not the most knowledgable DBA, I've had to learn almost completely on my own, AND on a production server, because it's the only MS SQL Server I have access to.
Everything was fine before I took down the production server for maintenance. Someone suggested that I re-index my tables because I was having some performance issues with a particularly large table (it didn't help that table btw), so I did re-index.
Now, Everything works wonderfully, except for the performance issue mentioned AND one other thing that is going horribly wrong.
Here is the table:
create table ABMcontactlink
(
classifier varchar(20) not null, /* Classification of contact. */
transmitter varchar(36) not null,
contact integer not null, /* Link to ABMcontact (detail) table */
primary key (classifier,transmitter,contact),
foreign key (contact) references ABMcontacts(identifier),
group_name varchar(20) null,
priority smallint null, /* Authorization level. */
type smallint null, /* Autoalarm or Manual */
last_modification_date datetime, /* Date/time record last touched */
last_modification_id varchar(40) /* Who last touched record */
)
go
create index IndexABMcontactlink on
ABMcontactlink(classifier,transmitter)
go
create index CandidateABMcontactlink on
ABMcontactlink(transmitter)
go
As you can see, I have the primary key, which creates a clustered index, PK_ABMContactlink_Some Number, and two other indexes.
Now, this is a very busy production database, and most quick short queries benefit more from CandidateABMContactlink than from the other two indexes.
Unfortunately, in this production system, and this table, seconds count ALOT, so when I have roughly 3000-4000 quereies an hour pulling information from this table, I personally beleive I need to keep CandidateABMContactlink, and I'm not willing to find out on a production server.
** Now to the Problem at Hand **
I have one query that kicks off about 7 times a day, used to take less than 1 minute before the re-index. NOW it takes 30 Minutes. And it drags the system to a crawl.
I did some looking into it, and this query is using CandidateABMContactlink, and it takes 30 minutes. If it uses PK_Abmcontactlink it finishes in under 45 seconds.
Most queries are simple, "Select Column_names from abmcontacts where identifier in (select contact from abmcontactlink where transmitter = 'XXXXXX')"
This one is:
select * from ABMcontacts where (
(last_modification_date >= '2006-04-28 04:40:03' and last_modification_date <= '2006-05-09 16:41:14')
and EXISTS(select contact from ABMcontactlink where contact = identifier
and EXISTS(select transmitter_id from ABMtransmitter where transmitter_id = transmitter and (dealer = 'XXXX'))))
or
(EXISTS(select contact from ABMcontactlink where
(last_modification_date >= '2006-04-28 04:40:03' and last_modification_date <= '2006-05-09 16:41:14')
and contact = identifier and EXISTS(select transmitter_id from ABMtransmitter where transmitter_id = transmitter and (dealer = 'XXXX'))))
I can't change the query, so how do I make it use the Index I want it to use without removing the index that it is using? (I know there are much better ways to write the above query, I'm not the culprit, if I could re-write it, I would)
Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return.
This is implemented as a stored procedure
declare @OwnerIdent varchar(7)
set @OwnerIdent='A12345B'
SELECT table1.val1 FROM table1 INNER JOIN table2
ON table1. Ident = table2.Ident
WHERE table2.Ident = @OwnerIdent
'Now for each result of the above I need to run the below query
SELECT Clients.Name , Clients.Address1 ,
Clients.BPhone, Clients.email
FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey
WHERE Growers.PIN = @newpin)
'@newpin being the result from first query
Any help appreciated
Usually it is better to include the columns in the index that are in where clause, select list and join.I am thinking that the columns in the selected list is better to keep as index columns and the columns that are in the where clause is better to keep in key columns.Where do we use join column is it better to create as main key column or included column.
View 4 Replies View RelatedI have an issue where I am getting an error on an unique index.
I know why I am getting the error but not sure how to get around it.
The query does a check on whether a unique value exists in the Insert/Select. If I run it one record at a time (SELECT TOP 1...) it works fine and just won't update it if the record exists.
But if I do it in a batch, I get the error. I assume this is because it does the checking on the file before records are written out and then writes out the records one at a time from a temporary table.
It thinks all the records are unique because it compares the records one at a time to the original table (where there would be no duplicates). But it doesn't check the records against each other. Then when it actually writes out the record, the duplicate is there.
How do I do a batch where the Insert/Select would write out the records without the duplicates as it does when I do it one record at a time.
CREATE TABLE #TestTable
(
Name varchar(50),
Email varchar (40)
)
Insert #TestTable (Name,Email) Values('Tom', 'tom@aol.com')
[Code] .....
When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into.
Thanks
Hi,
I have 2 identical DB on the same server with the same indexes on both db, but when I run a query using query analyzer, on db A it will take 6 sec but run the same query on db B it will take 1 minute. I checked the indexes on all tables in 2 db's are the same.
I used show execution plan, it does show that it is not using some indexes on the 2nd db, I droped all indexes and rebuild them again, but the same result.
Any idea why it is taking so long on the other db.
Thanks
I have a view (table_rol_contct) on a number of tables, one of which is a table of address data (table_address). This table has a unique, non-clustered index on Postcode, House Number and an Id column (idx_x_s_postcode). There is a clustered index on the Id column.
There are a number of queries which are run against this view, one of which selects addresses whose Postcode matches a full or partial Postcode entered through a Client Application.
This query :
Select distinct top 30
con.salutation+' '+con.first_name+' '+con.last_name as fullname,
con.title,
con.phone,
con.x_housename,
con.x_houseno,
con.x_housename+' '+con.x_houseno+' '+con.address+', '+con.address_2+' '+con.city as fulladdress,
from table_rol_contct con
where con.x_s_postcode like 'wf18%'
order by con.last_name asc, con.first_name asc
returns its results in under a second while this one:
Select distinct top 30
con.salutation+' '+con.first_name+' '+con.last_name as fullname,
con.title,
con.phone,
con.x_housename,
con.x_houseno,
con.x_housename+' '+con.x_houseno+' '+con.address+', '+con.address_2+' '+con.city as fulladdress,
from table_rol_contct con
where con.x_s_postcode like 'wf3%'
order by con.last_name asc, con.first_name asc
takes 38 seconds to returns its results.
The execution plans for these queries show that the slow query uses a Clustered Index Scan whereas the quick one uses an index seek on the ‘idx_x_s_postcode’ non-clustered index.
dbcc show_statistics (table_address, idx_x_s_postcode)
shows that this index has 300 steps with these representing the ones relevant to the above queries:
..., WF169AG, WF175NN, WF178BY, WF20HN, WF27ET, WF28TP, WF31PZ, WF34EF, WF42HL, ...
From this, you can see that ‘WF18%’ is wholly contained in a single step and the optimizer appears to happily perform an index seek, whereas ‘WF3%’ spans more than 1 so the optimizer seems to choose a clustered index scan.
Is there any way that I can force the use of the non-clustered index or, at least, make it more likely that the optimizer will use this index without coding an optimizer hint into the ‘Create View’ statement? The problem is that the view is used for other queries, all the queries run against it are generated from within a client application and the given search criteria could, for example, be ‘con.address’ rather than ‘con.x_s_postcode’.
Hi,I have a table t1 with columns a,b,c,d,e,f,g,h,i,j,k,lI have created a clustered index on a,b,d,e which forms the primarykey. I have created a covering index on all the columns of t1. Thereare 1 million rows in this table.My query chooses the TOP20 rows based on some filter conditions. WhenI use an "ORDER BY 1", it uses the clustered index and I get the resultin 1 second, whereas it takes around 1minute 48seconds when I use an"ORDER BY b or any other column". It is not using the covering / theclustered index.What is the best way to index this table so that it uses the index andI get the result within the shortest possible time (just like that ofORDER BY 1 which take hardly a second).Thanks..Sridhar
View 2 Replies View Relatedhi, what are the tools that I can use to Optimize the query/ index.
I know that if I am running a query on a table I create index on the fields where I use in the where clause, is this a right thinking.
Someone told me how do I determine what columns should be indexed, I told him the fields that I use in the where clause should be indexed to speed up the process of retrieving the data. ..... is this answer correct. if not please advice the correct one.
Thanks
Ahmed
have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003.
i have tried with the following query, i need help putting my queries together
select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004')
when i run this query i get this message
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15
please help
mustfa
Not sure which forum too put this question
I have recently upgraded my Access Database too use SQL server 2005 using the upsizing wizard. I have selected too use SQL Linked tables instead off the ADP project for ease off conversion. So now the tables are on the SQL server and the queries are still local.
On off my Access forms which returns a datasheet view by accessing the a query is now running really slowly now SQL server is the backend, I was wondering if I can put an index on one of the tables too speed it up, as the query is local too Access will this work? Just wondering if anyone ever come across this.
I am trying to resolve performance issues in a third party application. I have run the profiler and found a transaction that performs a table scan against a 6 million row table. This transaction occurs repeatedly, so I thought, just add an index on the columns in the where clause used here. After adding the index, I looked at the estimated execution plan in Query analyzer, and I find that it is still performing the table scan. If I run the query it takes over 60 seconds to run, if i add an index hint, it runs in under a second. I ran DBCC SHOW_STATISTICS to see if the statistics were up to date:
Statistics for INDEX 'IX_Finish_dept'.
Updated Rows Rows Sampled Steps Density Average key length
-------------------- -------------------- -------------------- ------ ------------------------ ------------------------
Jun 26 2007 5:18PM 6832336 6832336 150 2.1415579E-7 18.0
(1 row(s) affected)
All density Average Length Columns
------------------------ ------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2.1875491E-7 8.0 finish
1.9796084E-7 18.0 finish, dept
(2 row(s) affected)
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
------------------------------------------------------ ------------------------ ------------------------ -------------------- ------------------------
1900-01-01 00:00:00.000 0.0 106110.0 0 0.0
2001-02-01 17:00:00.000 54121.0 47.0 22951 2.3581107
2001-02-28 17:00:00.000 44436.0 22.0 18121 2.4520473
2001-04-06 00:00:00.000 56830.0 76.0 24902 2.2820544
2001-08-10 17:00:00.000 196491.0 19.0 88800 2.2127116
2001-09-02 17:00:00.000 33070.0 50.0 15289 2.162993
2001-10-05 17:04:59.997 57975.0 30.0 22882 2.5335402
2001-11-05 15:31:59.997 50178.0 21.0 20899 2.4008613
2001-12-10 17:00:00.000 55266.0 38.0 25114 2.2006052
2002-01-03 17:00:00.000 40322.0 51.0 18649 2.1620376
2002-02-25 17:00:00.000 86338.0 24.0 39266 2.1987979
2002-08-15 06:11:00.000 296085.0 166.0 124526 2.3776772
2002-10-07 21:18:59.997 88727.0 826.0 39017 2.2740018
2002-12-17 16:59:00.000 127671.0 6.0 53314 2.3946545
2003-01-16 07:15:00.000 62206.0 71.0 24604 2.5281854
2003-01-21 07:15:00.000 8287.0 43.0 3661 2.2629712
2003-01-27 07:15:00.000 10402.0 68.0 4265 2.4389215
2003-01-31 07:15:00.000 9127.0 73.0 3784 2.4113607
2003-02-05 00:00:00.000 8362.0 327.0 3500 2.3891428
2003-02-10 00:00:00.000 8846.0 262.0 3230 2.7386997
2003-02-14 00:00:00.000 10018.0 51.0 4107 2.4386563
2003-02-20 00:00:00.000 10388.0 91.0 4686 2.2168159
2003-02-26 00:00:00.000 10571.0 69.0 4330 2.4407759
2003-03-03 00:00:00.000 10476.0 261.0 4423 2.3679929
2003-03-06 00:00:00.000 8858.0 594.0 3183 2.7829092
2003-04-02 00:00:00.000 57681.0 275.0 38622 1.4934366
2003-04-05 00:00:00.000 10539.0 29.0 8776 1.2008888
2003-04-09 00:00:00.000 9880.0 1324.0 7193 1.3735576
2003-04-12 00:00:00.000 8953.0 195.0 7737 1.1571668
2003-04-16 00:00:00.000 8385.0 177.0 7154 1.1719078
2003-04-21 00:00:00.000 8920.0 173.0 7756 1.1500773
2003-04-24 00:00:00.000 8563.0 156.0 7320 1.169649
2003-04-29 00:00:00.000 8462.0 137.0 7414 1.1412003
2003-05-02 00:00:00.000 9625.0 140.0 8363 1.1509027
2003-05-06 00:00:00.000 8208.0 904.0 6557 1.251792
2003-05-09 00:00:00.000 9211.0 119.0 7986 1.1533934
2003-05-19 00:00:00.000 19623.0 123.0 17290 1.1348679
2003-05-22 00:00:00.000 9568.0 246.0 8357 1.1449084
2003-05-28 00:00:00.000 9599.0 169.0 8553 1.1221651
2003-06-02 00:00:00.000 10937.0 174.0 9599 1.1393895
2003-07-11 00:00:00.000 99592.0 999.0 83573 1.1916767
2003-07-29 00:00:00.000 42434.0 111.0 33918 1.2510761
2003-08-21 00:00:00.000 59580.0 323.0 50756 1.1738282
2003-09-12 00:00:00.000 51779.0 1407.0 44298 1.1688789
2003-09-25 00:00:00.000 30655.0 255.0 26924 1.138533
2003-10-12 00:00:00.000 44573.0 968.0 37746 1.1808668
2003-10-28 00:00:00.000 38358.0 532.0 32689 1.1734222
2003-11-11 00:00:00.000 35158.0 145.0 28124 1.2500622
2003-12-04 00:00:00.000 61304.0 787.0 52882 1.1592383
2003-12-18 00:00:00.000 44462.0 221.0 39493 1.1257913
2004-01-06 00:00:00.000 56617.0 998.0 49471 1.1444252
2004-02-04 00:00:00.000 96694.0 537.0 83182 1.162425
2004-03-05 00:00:00.000 90850.0 716.0 78693 1.1544864
2004-03-23 00:00:00.000 48969.0 125.0 43450 1.1270195
2004-07-05 00:00:00.000 301725.0 1405.0 258824 1.1657491
2004-08-06 00:00:00.000 95079.0 1419.0 75445 1.2602259
2004-09-03 00:00:00.000 88056.0 193.0 68403 1.2873119
2004-09-23 01:30:12.997 57515.0 8.0 42891 1.3409261
2004-10-11 00:00:00.000 57204.0 116.0 40241 1.4215
2004-10-15 00:00:00.000 17702.0 186.0 12774 1.3856752
2004-10-19 00:00:00.000 9556.0 125.0 7305 1.3079661
2004-10-21 00:00:00.000 8898.0 133.0 6299 1.4126052
2004-10-25 00:00:00.000 8878.0 104.0 6372 1.3930645
2004-10-27 00:00:00.000 11904.0 252.0 6056 1.9656539
2004-10-29 00:00:00.000 8866.0 99.0 6551 1.3533812
2004-11-02 15:22:47.997 12287.0 1.0 9791 1.2547998
2004-11-05 13:16:50.997 12287.0 1.0 10013 1.2269822
2004-11-09 23:52:48.000 12284.0 4.0 9200 1.3352174
2004-11-12 17:17:59.997 12287.0 1.0 9360 1.3127136
2004-11-22 06:58:06.997 24575.0 1.0 19742 1.244745
2004-11-25 01:57:00.000 12287.0 1.0 8822 1.392768
2004-11-30 21:34:59.997 12287.0 1.0 9128 1.3459306
2004-12-03 13:21:24.000 12287.0 1.0 9085 1.3523003
2004-12-07 04:05:21.000 12285.0 5.0 9488 1.2947934
2004-12-09 13:25:00.000 12285.0 5.0 8993 1.3659106
2004-12-13 07:21:46.000 12282.0 10.0 9461 1.2981714
2004-12-15 18:41:23.000 12287.0 2.0 9112 1.3482937
2005-02-04 14:41:36.997 178768.0 58.0 133439 1.3396883
2005-02-23 00:00:00.000 51107.0 29.0 38624 1.3231586
2005-03-10 23:06:17.997 50891.0 24.0 38479 1.3225312
2005-03-28 00:00:00.000 45509.0 32.0 34203 1.3305169
2005-04-13 09:50:34.000 58778.0 19.0 43687 1.3454038
2005-06-08 09:46:43.997 162983.0 25.0 121508 1.3413246
2005-08-08 09:37:29.000 197467.0 20.0 143462 1.3764411
2005-08-24 11:21:37.997 57393.0 5.0 42770 1.3418672
2005-09-11 13:54:05.997 53729.0 5.0 39527 1.3592987
2005-11-08 00:00:00.000 193537.0 69.0 136906 1.4136385
2005-11-22 00:00:00.000 55031.0 33.0 38197 1.4407152
2005-12-05 00:00:00.000 40371.0 77.0 28082 1.4376112
2005-12-22 12:40:59.997 75170.0 3.0 52523 1.4311825
2006-03-02 00:00:00.000 239709.0 42.0 170405 1.4066935
2006-03-04 06:26:36.997 9639.0 23.0 6470 1.489799
2006-03-12 10:02:43.000 21993.0 1.0 16086 1.3672137
2006-03-15 00:00:00.000 8774.0 40.0 6687 1.3119019
2006-04-03 00:00:00.000 69570.0 31.0 46495 1.4962578
2006-04-04 00:00:00.000 8743.0 28.0 4606 1.8977643
2006-04-04 13:53:00.997 12284.0 6.0 3401 3.6108172
2006-04-05 00:00:00.000 10794.0 29.0 3438 3.139616
2006-04-06 00:00:00.000 9413.0 45.0 5001 1.8818473
2006-04-10 00:00:00.000 11058.0 30.0 7865 1.4059758
2006-04-14 00:00:00.000 23183.0 38.0 16281 1.4238423
2006-04-18 00:00:00.000 9898.0 37.0 7258 1.3635488
2006-04-21 03:19:31.000 16561.0 26.0 11848 1.3976707
2006-04-25 14:48:00.000 12287.0 3.0 8553 1.436572
2006-04-27 13:37:49.000 9793.0 96.0 7203 1.3593837
2006-05-02 00:00:00.000 11426.0 30.0 8135 1.4043757
2006-05-04 05:28:36.000 12277.0 22.0 8806 1.3940048
2006-06-08 00:00:00.000 123695.0 33.0 89478 1.3824068
2006-06-16 00:00:00.000 35327.0 37.0 24539 1.4396267
2006-06-29 00:00:00.000 48433.0 40.0 35226 1.3748829
2006-07-14 00:00:00.000 62915.0 57.0 44859 1.4024744
2006-08-10 00:00:00.000 106281.0 36.0 75810 1.401939
2006-08-17 00:00:00.000 25345.0 81.0 18123 1.398422
2006-08-28 00:00:00.000 40947.0 38.0 27573 1.4850397
2006-09-11 09:00:00.000 52187.0 15913.0 36698 1.4220666
2006-09-25 00:00:00.000 52902.0 30.0 37210 1.4216764
2006-10-06 00:00:00.000 54534.0 31.0 38244 1.4259119
2006-10-11 13:29:40.997 16380.0 5.0 12503 1.3100855
2006-11-29 00:00:00.000 197522.0 27.0 138746 1.423623
2006-12-01 00:00:00.000 10584.0 24.0 7602 1.3920821
2007-01-02 00:00:00.000 141284.0 34.0 101246 1.3954526
2007-01-12 02:57:03.997 60416.0 23.0 41700 1.4488249
2007-02-13 00:00:00.000 156270.0 75.0 109875 1.4222525
2007-02-16 00:00:00.000 17770.0 38.0 12325 1.441668
2007-03-05 12:23:00.000 73763.0 3.0 51503 1.43218
2007-03-08 04:11:49.997 16407.0 22.0 11428 1.4355587
2007-03-26 09:10:43.000 76336.0 20.0 53687 1.4218712
2007-04-05 12:31:28.000 64126.0 24.0 40172 1.5962859
2007-04-07 01:11:22.000 9244.0 28.0 6657 1.388405
2007-04-10 00:00:00.000 8924.0 38.0 6140 1.4531835
2007-04-24 21:01:00.000 73487.0 6.0 51689 1.421687
2007-04-26 09:01:48.997 9584.0 25.0 6650 1.441203
2007-04-28 04:09:21.000 9801.0 27.0 7037 1.3925831
2007-05-01 12:55:00.000 8781.0 26.0 6012 1.460336
2007-05-03 00:00:00.000 10570.0 53.0 7298 1.4481436
2007-05-04 21:49:27.000 12287.0 1.0 8680 1.415553
2007-05-08 06:06:45.997 8202.0 27.0 5511 1.4880261
2007-05-10 00:00:00.000 10920.0 49.0 7973 1.3696225
2007-05-12 00:44:10.000 11375.0 27.0 8223 1.3833151
2007-05-15 10:51:50.000 9453.0 27.0 6516 1.4507366
2007-05-18 08:44:36.997 17930.0 27.0 12651 1.4172792
2007-05-22 00:00:00.000 10089.0 74.0 7260 1.3894781
2007-05-23 21:07:38.000 12286.0 3.0 8604 1.4279405
2007-05-26 03:46:02.000 12287.0 6.0 8545 1.4377487
2007-05-30 21:24:29.997 12287.0 1.0 8663 1.4183308
2007-06-01 18:37:16.000 12287.0 1.0 8401 1.4623899
2007-06-05 00:00:00.000 9255.0 52.0 6491 1.4256008
2007-06-08 22:18:40.000 24574.0 3.0 17047 1.4415439
2007-06-12 09:42:14.997 9550.0 31.0 6410 1.4896272
9200-12-08 09:49:59.997 64286.0 1.0 45408 1.4157417
(150 row(s) affected)
What can I do to get SQL to use this index?
I'm looking for a query that will return all index names, the table the index is on and the columns in the index...
View 1 Replies View Relatedcreate table t1(a varchar(50) , b varchar(50))
create index i1 on t1(a)
create index i2 on t1(b)
create view v1
as
select * from t1 where isnull(a,b) = 'test'
select * from v1
The above SQL "select * from v1" is doing a table scan.
What do I do to make it perform an index seek ????
TIA
- ForXLDB
I am trying to create an index for a specific query that is used a lot.
Unfortunately I am keep getting a clustered index scan. (4.6 mil rows)
Googling "AND" and "OR" is tricky, but did lead met to many articles about filtered indexes.
None of which gave me a clear answer.
How can I make an index for this query?
Here's the code setup
------------------------------------------------------------------------------------------
-- Setup
------------------------------------------------------------------------------------------
IF OBJECT_ID('FilterTbl') IS NOT NULL
DROP TABLE FilterTbl
CREATE TABLE FilterTbl
(
SurroIDInt IDENTITY PRIMARY KEY,
CompIDSmallInt,
[code]....
This is my table:
use tempdb
go
if object_id('Data', 'u') is not null drop table Data
go
with temp as (
select top 10000 row_number() over (order by c1.object_id) Id
from sys.columns c1 cross join sys.columns c2
[code]....
What index would be best for these three queries? With best I mean the execution time, I don't care about additional space.
This is the index I currently use:
create nonclustered index Ix_Data on Data (StateId, PalletId, BoxId, Id)
The execution plan is SELECT (0%) - Stream Aggregate (10%) - Index Scan (90%).
Can this be optimized (maybe to use Index Seek method)?
Running SQL 2012 SP2
I've got this query that runs in 30 seconds and returns about 24000. The table variable returns about 145 rows (no performance issue here), and the TransactionTbl table has 14.2 Million rows, a compound, clustered primary key, and 6 non-clustered indexes, none of which meet the needs of the query.
declare @CltID varchar(15) = '12345'
declare @TranDate datetime = '2015-07-25'
declare @Ballance table
(Ledger_Code varchar(4),
AssetID varchar(32),
CurrencyID varchar(3) )
[Code] ....
Actual execution plan shows SQL is doing an index seek, then a nested loop join, and then fetching the remaining data from the TransactionTbl using a Key Lookup.
I designed a new indexes based on the query, which when I force it's usage via an index hint, reduces the run time to sub-second, but without the index hint the SQL optimiser won't use the new index, which looks like this:
CREATE INDEX IX_Test on GLSchemB.TransactionTbl (CltID, Date) include (Ledger_Code, Amount, CurrencyID, AssetID)and I tried this:
CREATE INDEX IX_Test on GLSchemB.TransactionTbl (CltID, Date, Ledger_Code, CurrencyID, AssetID) include (Amount)and even a full covering index!
I did some testing, including disabling all indexes but the PK, and the optimiser tells me I've got a missing index and recommends I create one EXACTLY like the one I designed, but when I put my one back it doesn't use it.
I though this may be due to fragmentation and/or stats being out of date, so I rebuilt the PK and my index, and the optimiser started using my index, doing an index seek and running sub-second. Thinking I had solved the problem I rebuilt all the indexes, testing after each one, and my index was used BUT as soon as I flushed the related query plan, the optimiser went back to using a less optimal index, with a seek and key lookup plan and taking 30 seconds.
For now I've resorted to using the OPTION (TABLE HINT(G, INDEX(IX_Test))) to force this, but it's a work around only. Why the optimiser would select a less optimal query plan?
I have a table that seems to have a bad index. When I do the followingquery I get inconsistant and needless to say incorrect results.select count(*) from mytable where mycolumn = 1If I remove the index from "mycolumn" the query works correctly. If Iadd the index back (even with a new name etc...) it doesn't workright.Has anyone ran into this? or does anyone know how I can fix thisproblem?It seems that removing the index is not really removing everythingbecause when I add a new one I get this same problem... btw, this isisolated to this column on this table. all other indexes within thedatabase are fine.Any help would be appreciated.Thanks,dharper
View 1 Replies View RelatedI have one query that looks like this.
DECLARE @propname
SET @propname = NULL -- can be Null or have wild card values
SELECT col1, col2, col3, propertyname FROM testtable where col1 = 1 and col2 = 2 and col3 = 3 and (@propname IS NULL OR propertyname LIKE @propname)
col1, col2, col3 are part of a clustered index
propertyname is a nonclustered index
This is the predicament. If the "@propname is NULL" is first in the OR statement then the query will use the clustered index for finding the record. If I put "@propname is NULL" last then it uses the propertyname index no matter what. So I either get full index scans if NULL is ever used on property names or I get consistent two second long searches on the clustered index. Any way to have the best of both worlds? Or do I have to divide up my query into more stored procedures?
THANKS