Is There A Way To Change The Index A Query Uses Without Modifying The Query?
May 9, 2006
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)
View 1 Replies
ADVERTISEMENT
May 29, 2008
Hi,
I have a query that I am working on that involves 2 tables. The query below is working correctly and bringing back the desired results, except I want to add 1 more column of data, and I'm not exactly sure how to write it.
What I want to add is the following data.
For each row that is brought back we want to have the COUNT(*) of users who joined the website (tbluserdetails) where their tbluserdteails.date is > the tblreferemails.referDate
Effectively we are attempting to track how well the "tell a friend" via email feature works, and converts to other joined members.
Any assistance is much appreciated!!
thanks once again
mike123
SELECT CONVERT(varchar(10),referDate,112) AS referDate,
SUM ( CASE WHEN emailSendCount = 0 THEN 1 ELSE 0 END ) as '0',
SUM ( CASE WHEN emailSendCount = 1 THEN 1 ELSE 0 END ) as '1',
SUM ( CASE WHEN emailSendCount = 2 THEN 1 ELSE 0 END ) as '2',
SUM ( CASE WHEN emailSendCount = 3 THEN 1 ELSE 0 END ) as '3',
SUM ( CASE WHEN emailSendCount > 3 THEN 1 ELSE 0 END ) as '> 3',
SUM ( CASE WHEN emailSendCount > 0 THEN 1 ELSE 0 END ) as 'totalSent',
count(*) as totalRefers,
count(distinct(referUserID)) as totalUsers,
SUM ( CASE WHEN emailAddress like '%hotmail%' THEN 1 ELSE 0 END ) as 'hotmail',
SUM ( CASE WHEN emailAddress like '%hotmail.co.uk%' THEN 1 ELSE 0 END ) as 'hotmail.co.uk',
SUM ( CASE WHEN emailAddress like '%yahoo.ca%' THEN 1 ELSE 0 END ) as 'yahoo.ca',
SUM ( CASE WHEN emailAddress like '%yahoo.co.uk%' THEN 1 ELSE 0 END ) as 'yahoo.co.uk',
SUM ( CASE WHEN emailAddress like '%gmail%' THEN 1 ELSE 0 END ) as 'gmail',
SUM ( CASE WHEN emailAddress like '%aol%' THEN 1 ELSE 0 END ) as 'aol',
SUM ( CASE WHEN emailAddress like '%yahoo%' THEN 1 ELSE 0 END ) as 'yahoo',
SUM ( CASE WHEN referalTypeID = 1 THEN 1 ELSE 0 END ) as 'manual',
SUM ( CASE WHEN referalTypeID = 2 THEN 1 ELSE 0 END ) as 'auto'
FROM tblreferemails R
WHERE DateDiff(dd, referDate, GetDate()) < 5
GROUP BY CONVERT(varchar(10),referDate,112)
ORDER BY referDate DESC
CREATE TABLE [dbo].[tblUserDetails]
(
[UserID] [int] IDENTITY(1,1) NOT NULL,
[NameOnline] [varchar](15) NULL,
[EmailAddress] [varchar](50) NULL,
[Date] [datetime] NULL,
[Active] [tinyint] NULL
)
CREATE TABLE [dbo].[tblReferEmails](
[emailID] [int] IDENTITY(1,1) NOT NULL,
[referUserID] [int] NOT NULL,
[destinationName] [varchar](50) NULL,
[emailaddress] [varchar](50) NOT NULL,
[referDate] [datetime] NOT NULL,
[referalTypeID] [int] NULL,
[deleted] [int] NULL,
[emailSendCount] [int] NULL,
[lastDateSent] [smalldatetime] NULL
) ON [PRIMARY]
GO
View 3 Replies
View Related
Nov 19, 2007
Greetings
I have 3 tables. The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on
I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType grouped by the CallDetailID and ordered by the date created
SCRIPTS
DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30),
StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)
INSERT @Request
SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL
SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL
SELECT '456456', 'Bobby', 'SA024', 12, 114, Null UNION ALL
SELECT '22322362', 'Guck', 'SA024', 44, 123, Null UNION ALL
SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL
SELECT '225652', 'Jim', 'SA055', 67, 143, Null UNION ALL
SELECT '126756', 'Jasm', 'SA055', 67, 145, Null UNION ALL
SELECT '786234', 'Chuck', 'SA055', 67, 154, Null UNION ALL
SELECT '66234', 'Mutuk', 'SA059', 72, 185, Null UNION ALL
SELECT '2232362', 'Buck', 'SA055', 67, 195, Null
DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)
INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 1, 12123 UNION ALL
SELECT 114, 2, 12123 UNION ALL
SELECT 123, 2, 12123 UNION ALL
SELECT 134, 3, 12123 UNION ALL
SELECT 143, 1, 6532 UNION ALL
SELECT 145, 1, 6532 UNION ALL
SELECT 154, 2, 6532 UNION ALL
SELECT 185, 2, 6532 UNION ALL
SELECT 195, 3, 6532
DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT, EntryDt DateTime)
INSERT @CallDetail
SELECT 12123, 1, '11/5/2007 10:41:34 AM' UNION ALL
SELECT 6532, 1, '11/5/2007 12:12:34 PM'
--
--select * from @Request
Query written to achieve the requirement
UPDATE r
SET r.UniqueNo = p.RecID
FROM @Request AS r
INNER JOIN (
SELECT r.RequestID,
ROW_NUMBER() OVER (PARTITION BY cd.EmpID, r.StateNo, r.CityNo, c.CallDetailID, c.CallType ORDER BY cd.EntryDt) AS RecID
FROM @Request AS r
INNER JOIN @Call AS c ON c.CallID = r.CallID
INNER JOIN @CallDetail AS cd ON cd.CallDetailID = c.CallDetailID
) AS p ON p.RequestID = r.RequestID
WHERE r.UniqueNo IS NULL
select * from @Request
OUTPUT :
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 2
456456 Bobby SA024 12 114 1
22322362 Guck SA024 44 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA055 67 143 1
126756 Jasm SA055 67 145 2
786234 Chuck SA055 67 154 1
66234 Mutuk SA059 72 185 1
2232362 Buck SA055 67 195 1
EXPECTED OUTPUT: (See the last column for unique nos).
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 1
456456 Bobby SA024 12 114 1
22322362 Guck SA024 44 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA055 67 143 2
126756 Jasm SA055 67 145 2
786234 Chuck SA055 67 154 2
66234 Mutuk SA059 72 185 2
2232362 Buck SA055 67 195 2
How can I modify the query to achieve the expected output?
Thanks:
View 9 Replies
View Related
May 28, 2008
Hi,
I have the following query which is working fine, but its returning a row for each day.
I'd like to modify it somehow so that we had the same data returned, but 1 row for each month. Can anyone offer some insight ?
Any help is much appreciated !!
thanks once again,
mike123
CREATE PROCEDURE [dbo].[select_Stats_LoginHistory]
(
@numDays int
)
AS SET NOCOUNT ON
SELECT CONVERT(varchar(10),LL.loginDate,112) as loginDate,
COUNT(LL.userID) AS TotalLogins,
COUNT(DISTINCT LL.userID) AS TotalLogins_Unique
FROM tblLogins_Log LL WITH (NOLOCK)
WHERE DateDiff(dd, LL.loginDate, GetDate()) < @numDays
GROUP BY CONVERT(varchar(10),LL.loginDate,112)
ORDER BY loginDate DESC
table structure below
CREATE TABLE [dbo].[tblLogins_Log](
[loginID] [int] IDENTITY(1,1) NOT NULL,
[userID] [int] NULL,
[IP] [varchar](15) NOT NULL,
[loginDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
View 1 Replies
View Related
Sep 13, 2007
Please help figure out what is wrong with my code. The script is supposed to load a package (from file). The loaded package already has everything set up to run a query against a local server and output the results to an Excel file. The reason for the outer script is because I need to change the query based on a global variable. When the query changes, though, I think the existing dataflow Path is no longer valid, so I should remove it and re-create another one with the new input mappings. Here is my code, which runs and throws an exception at the AcquireConnections call.
The error is
Error: 0x2 at Script Task: The script threw an exception: Exception from HRESULT: 0xC020801B
I pieced together this code from the examples in the online books, but I am not sure what to do.
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Public Class ScriptMain
Public Sub Main()
'
Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Application()
Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _
app.LoadPackage("c:systimeExcelOutExcelOutExcelOutDo.dtsx", Nothing)
Dim pkgVars As Variables = package.Variables
Dim gsVar As Variable = pkgVars("User::gsExcelFile")
Dim currVars As Variables = Dts.Variables
Console.WriteLine(Dts.Variables("User::gsExcelFile").Value)
gsVar.Value = Dts.Variables("User::gsExcelFile").Value
pkgVars("User::gsQuery").Value = Dts.Variables("User::gsQuery").Value
pkgVars("User::gsCreateTable").Value = Dts.Variables("User::gsCreateTable").Value
Dim e As Executable = package.Executables("ExcelOutTask")
Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _
CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)
Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)
' Get the source component.
Dim SourceComponent As IDTSComponentMetaData90 = _
dataFlowTask.ComponentMetaDataCollection("Local Source")
Dim srcDesignTime As CManagedComponentWrapper = SourceComponent.Instantiate()
srcDesignTime.ProvideComponentProperties()
' Reinitialize the metadata.
srcDesignTime.AcquireConnections(vbNull)
srcDesignTime.ReinitializeMetaData()
srcDesignTime.ReleaseConnections()
' Get the destination component.
Dim destination As IDTSComponentMetaData90 = _
dataFlowTask.ComponentMetaDataCollection("Excel Destination")
Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate()
destDesignTime.ProvideComponentProperties()
' Create the path.
dataFlowTask.PathCollection.RemoveAll()
Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()
path.AttachPathAndPropagateNotifications(SourceComponent.OutputCollection(0), _
destination.InputCollection(0))
'Console.WriteLine(dataFlowTask.PathCollection.Count)
Dim ret As DTSExecResult
ret = package.Execute()
Console.WriteLine(ret.ToString)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
View 3 Replies
View Related
Oct 29, 2015
We have enabled Change Data Capture for auditing our table changes in SQL Server 2008. There is a request to NULL out a few columns (for all rows) in a couple CDC tables, due to compliance with a certification. Is there a compelling reason not to modify these tables and to leave the audit trail as-is?
View 1 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
Aug 24, 2001
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
View 1 Replies
View Related
Mar 12, 2001
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’.
View 1 Replies
View Related
Jul 23, 2005
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 Related
Aug 11, 2005
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 Replies
View Related
Nov 4, 2000
hi, 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
View 1 Replies
View Related
Feb 9, 2005
Greetings
I have encountered the following problem:
I currently develop an application for my company that actually uses rather long queries, with many records.
I have a particular query (Written using SQL string inside the .NET application rather than Stored proceedures),that needs to run in 2 databases (both SQL Server):
The first one is a test database that we use when in developing time quota to test our data
The second one is the real thing a data base that contains lots of records.
When criteria are placed in the query, it returs few records in both the databases , but if no criteria are placed (So it fetches all the records..) In the test Database works ok, but in the real one it "jams" till 30 seconds pass and I get a time out message...
I tried to change the Query time out time from inside the SQL Server from
Tools/Options/Advanced
but it doesn't seem to work out... it still times out after 30 secs
Any Ideas?
Thanx in advance :D
View 4 Replies
View Related
Apr 10, 2007
I'm converting a set of queries from Access to work as stored procedures on SQL server, and one of them that uses IIF gives me a syntax error. Here's the WHERE clause of the SELECT:
WHERE IIF(@myExtNum > 0,D.ExtentionNumber=@myExtNum,'') AND ...
I get the error message "syntax error near '>'."
It would seem this query wants to do two things: 1) if @myExtNum is >0, return only the rows for which D.ExtentionNumber equals a user specified value, or 2) if @myExtNum is 0 ignore this part of the condition.
I can rewrite this using multiple ANDs and ORs, but I wodered if there was a way I could get IIF working.
Something in my gut tells me this is not going to work, that IIF is being used to modify the query; that is, change which rows are returned; it is not being used to change how a given data value is displayed, which I think was the purpose for which IIF was originally intended.
Does anyone have an insight on this?
View 1 Replies
View Related
Feb 27, 2007
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.
View 1 Replies
View Related
Jun 27, 2007
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?
View 4 Replies
View Related
Sep 17, 2005
I would like to know about Reporting Services. Can we change the Dataset queries used in a report at run time.
View 1 Replies
View Related
Oct 2, 2007
Hi there
Assuming this is the query used to return this result:
select dept.name, count(prj.id) from department dept, project prj where prj.id = dept.uid group by dept.name
Dept, TotalProj
A , 10
B , 5
C , 2
Question: How do I change the query so that the result will return as follows:
Dept/TotalProj
A, B, C
10, 5, 2
View 5 Replies
View Related
May 8, 2007
Hi,
I have a reporting services report that i show in an asp.net page by a reportviewer control.
Is there a property that i can set in my asp.net page, to change the query for changing the data in my report.
The number and names of the columns will be the same.
I should work with parameters, but the query changes more than just a few where-clauses. So i think working with parameters looks impossible.
Thanks,
Dennis
View 3 Replies
View Related
Nov 12, 2001
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 Related
Jun 21, 2004
create 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
View 11 Replies
View Related
Jul 28, 2014
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]....
View 3 Replies
View Related
Dec 28, 2014
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)?
View 7 Replies
View Related
Jul 28, 2015
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?
View 8 Replies
View Related
Jul 20, 2005
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 Related
Apr 29, 2008
I 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
View 11 Replies
View Related
Nov 16, 2007
I have the following table:
Create Table Item(
I_Code NVarChar(40) Primary Key NOT NULL,
I_MatID NVarChar(40),
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20),
I_CompanyFound nvarchar(20),
I_Info1 nvarchar(55),
I_Acquired nvarchar(35),
I_Info2 nvarchar(55),
I_Info3 nvarchar(55),
I_Date DateTime DEFAULT GetDate()
);
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);
Create Index ind_Item_CompanyFound on Item(I_CompanyFound);
create Index ind_Item_i1 on Item(I_Company,I_CompanyFound);
create Index ind_Item_i2 on Item(I_CompanyFound,I_Company);
Now this query DOES NOT use index:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' or I_CompanyFound='102' )
While this one use:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' )
UNION
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_CompanyFound='102' )
Both return the same rows. Is this a bug? I found the following:
http://support.microsoft.com/kb/223423
Some feedback?
Thanks
View 5 Replies
View Related
Dec 23, 2004
Hi,
Our app has been distributed on more then 300 different sites.
On one of the sites we get the error "Could not continue scan with NOLOCK due to data movement" indicating that the query optimizer takes a NOLOCK for our select statement ( has been opened with adOpenDynamic, adLockOptimistic ).
It's no option to change the source, we have to solve this without touching the code.
Is there any way to tweak the query optimizer so that our app works correctly?
I know that there will be a reduction of performance but it's our only choose.
thnx in advance,
adOpenDynamic, adLockOptimistic
View 4 Replies
View Related
Apr 6, 2004
Hi everyone,
I’ve got a small question: Is it possible to change the Column_Name – Data Type from a SQL table with a query?
I want to do this with a query.
The Data Type ‘ntext’ must be changed to ‘text’
Thanks in advance!
View 4 Replies
View Related
Apr 16, 2015
I have a query which runs exactly as I want it to. Let's say it returns this in a table:
IDNumber
123
212
312
I want to change (find and replace?) all the number 12s in the 'Number' column for a string. I want to change the 12s to the word: HelloWorld. I don't want to update the table, merely the table that is being returned in the query.
View 1 Replies
View Related
Apr 11, 2007
Hi all,
I tried to change the size of a field of my table using the query :-
ALTER TABLE test MODIFY id varchar(50)
Initially the size of id was set to 30 .Is there any other way or any error in my query.Please help me soon.
joshymraj
View 2 Replies
View Related
Jun 22, 2007
I need someone that knows something about SQL queries.
I have a client that is running a Database known as ProLaw. It is in part a
document management system for Law Offices.
They have an SQL 2005 database that tracks per client all the documents they
create.
We had to replace there server with new server. The new server is running
sbs2003 and had to have a different Netbios name then the old sbs2000
server. (Small Bus. Server has some weird quirks that make simply using the
same netbios name impossible. Google search it if you don't believe me.)
The database holds in a single column the full network share path to each
document.
A document for example may have had a path of
"\lawwillsbs2000ProlawDocumentsACME wigets Incsmith_deposition.doc"
Different documents may have different names and more subdirectories but the
root path of "\lawwillsbs2000ProlawDocuments" is shared by all.
The new server is named \sbs2003 I need to change
the first part of almost 3000 path statements to the new server. The rest
of the path is unchanged.
I have had several people running prolaw tell me that I should run this
query:
UPDATE Events
SET DocDir=REPLACE(DocDir, '\\lawwillsbs2000', '\\sbs2003')
WHERE EventKind='O'
This doesn't work. Nothing is changed. I'm guessing it is because this
query assumes the value will be ONLY \lawwillsbs2000 I see nothing in here that tells the query that this is only part of the string. No wild card or other marker.
I need some kind of string function here do I not? Anyone know enough to
help me craft a proper query?
Thanks
Nathan Williams
View 3 Replies
View Related
Jul 20, 2005
/*Given*/CREATE TABLE [_T1sub] ([PK] [int] IDENTITY (1, 1) NOT NULL ,[FK] [int] NULL ,[St] [char] (2) NULL ,[Wt] [int] NULL ,CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED([PK]) ON [PRIMARY]) ON [PRIMARY]GOINSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)/*Is something like the following possible.The point is to change the value of the variableinside the query and use it in the calculated field.This doesn't compile of course, but is therea way to accomplish the same thing?*/DECLARE @ndx intSET @ndx = 1SELECT(a.FK+ (CASE WHEN @ndx > 0THEN (SELECT @ndx = b.WtFROM _T1sub bWHERE b.Wt = a.Wt)ELSE 0 END)) as FKplusWTFROM _T1sub a/*Output would look like this:*/FKplusWT-----------112233/*I know, I can get this output just by addingFK+WT. This is not about that.This is about setting vars inside a query*/thanks, Otto Porter
View 1 Replies
View Related
