Wednesday, March 20, 2013

[T-SQL] SQL Refusing to use an index

[T-SQL] SQL Refusing to use an index

SQL Refusing to use an index

Posted: 19 Mar 2013 10:14 PM PDT

I've just had a designer come to me with a really odd problem. A query, joining two tables on a primary key, example below:SELECT <fields> FROM <table1> INNER JOIN <table2> ON <table1.primarykey> = <table2.indexed field>The query runs optimally, within a couple of seconds .. until .. an additional field is added to the SELECT fields from <table2>.I can see what is happening. Until the additional field is added, all of the fields in the SELECT are available in an index; it is an old index migrated from SQL2000 with the fields as part of the index list and not simply INCLUDEd. Stats show the index is being used and to good use.However the moment the new field from table 2 is added, which is not being covered by the index, rather than the optimizer looking for another qualifying index, it is changing from the non-clustered index to a clustered index scan of table 2 which is processing nearly 2 million rows instead of the small subset it was doing before, and the execution time increases twenty fold.As this is running under a development environment we have tried everything from 1: Creating a similar index with all the fields (a very wide index and not my first choice), but this is ignored.2: Adding the additional fields to the existing index in the INCLUDE - also ignored.3: Dropped the previously working index - This did cause it to revert back to a better existing index - but obviously any other queries benefiting from it would now degrade, and the moment we recreated the index it was back to it's primary key scan.I've cleared the cache plans, updated statistics, rebuilt the indexes, everything I can think of.It will work if we put an index hint in place and tell it the index we think it should be using, and the performance is back to a couple of seconds. I know hints are the last resort, but I can't think of anything else I can investigate before we choose this as the final solution.Any advice would be greatly appreciated. :-D

T-SQL substring replace function

Posted: 19 Mar 2013 11:59 AM PDT

Hello, I have some data and need to extract the first 9 numbers from this pattern across multiple rows of data. I would think that using something like this:substring(value_expression, ,1,9)might work but am unaware of how to find the first 9 digits in this sequence:01J416781|1|2013|2|I am looking to find '01J416781', each row of data starts with this pattern. I would greatly appreciate any help.

Table Join using unique values or where Null = all values

Posted: 20 Mar 2013 12:35 AM PDT

Hi folks,I have a complex query that involves many tables but I am struggling with one particular join (TableA and TableB). There is only one field in each table where a join can be made. TableA.Area contains values that can be matched with TableB. However TableA may also contain a Null value in the joining column. If there is a Null value, then TableA.Area needs to join with [b]all[/b] values in TableB.ColId. [code="sql"]USE [DB1]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[TableA]( [Id] [tinyint] IDENTITY(1,1) NOT NULL, [Area] [nvarchar](10) NULL, [Item] [nvarchar](10) NOT NULL, [Description] [nvarchar](50) NOT NULL, CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED ( [Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]) ON [PRIMARY];INSERT INTO dbo.TableA(Area, Item, [Description])VALUES('A1', 'B1232', 'Thingymebobs');INSERT INTO dbo.TableA(Area, Item, [Description])VALUES('A4', 'B4352', 'Whatits');INSERT INTO dbo.TableA(Area, Item, [Description])VALUES(Null, 'B8769', 'Dunno');USE [DB1]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[TableB]( [ColId] [nvarchar](10) NOT NULL, [DeliveryName] [nvarchar](60) NOT NULL, CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED ( [ColId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]) ON [PRIMARY];INSERT INTO dbo.TableB(ColId, DeliveryName)VALUES('A1', 'Acme1');INSERT INTO dbo.TableB(ColId, DeliveryName)VALUES('A4', 'Acme4');INSERT INTO dbo.TableB(ColId, DeliveryName)VALUES('A8', 'Acme8');INSERT INTO dbo.TableB(ColId, DeliveryName)VALUES('A9', 'Acme9');[/code]The columns to be joined are TableA.Area AND TableB.ColId[code="sql"]SELECT b.DeliveryName, a.Item, a.[Description]FROM TableA a JOIN Tableb b ON --????;[/code]Any ideas please?Thanks in advance,

Help with query using exclusion table

Posted: 20 Mar 2013 12:38 AM PDT

I have a metrics database and a few reports running from it.One of them is to report on databases that haven't had a full backup run in the last week...It checks other tables for Production and Online dbs etc etc...My query looks something like:[code="sql"]SELECT bh.Server AS Server ,bh.database_name ,MAX(bh.backup_finish_date) AS last_db_backup_date ,datediff(dd,MAX(bh.backup_finish_date),Getdate()) AS Age ,bh.backup_type FROM Backup_History bh INNER JOIN ServerList_SSIS sl ON bh.Server = sl.ServerDisplayName AND sl.Environment = 'PROD' AND sl.BackupReports = 'Y' INNER JOIN Database_Info dbi ON bh.Server = dbi.Server AND bh.database_name = dbi.DatabaseName AND dbi.Status <> 'OFFLINE' WHERE bh.backup_type = 'Database' GROUP BY bh.Server ,bh.database_name ,bh.backup_type HAVING (MAX(bh.backup_finish_date) < DATEADD(dd, - 8, GETDATE())) ORDER BY Age DESC ,bh.Server ,bh.database_name[/code]The output right now is:Server database_name last_db_backup_date Age backup_typeSERVER1 DB1 2013-03-09 18:32:50.000 11 DatabaseSERVER1 DB2 2013-03-09 13:03:16.000 11 DatabaseSERVER1 DB1 2013-03-09 14:31:14.000 11 DatabaseSERVER2 DB2 2013-03-09 12:56:19.000 11 DatabaseSERVER2 DB3 2013-03-09 13:30:17.000 11 Database(Sorry about the formatting)Now I need to exclude the DB1 database so I created an exclusion table with Server and DB columns and added that server and database.I need help with the query to exclude that one from the report:I've tried:[code="sql"]AND (bh.Server NOT IN(SELECT SERVER from ReportExclusionList)AND bh.database_name NOT IN(SELECT DatabaseName FROM ReportExclusionList))[/code]After the WHERE clause but it excludes all databases from the specified server...I Just can't wrap my head around the sql... (Maybe change the NOT IN portion to include the database and server...)Any help would be appreciated.Thanks

help with query - am I going down the right path on this

Posted: 19 Mar 2013 11:20 PM PDT

I'm working on a query that will be comparing columns in tables in 2 seperate databases. If the where clause is met, I need to create a log file and then email it at the end of the procedure.here is my query:SELECT db1.*, db2.*FROM db1..ORGANIZATION db1inner join db2..ORGANIZATION db2ON db1.ORG_ID = db2.ORG_IDwhere db1.TITLE <> db2.TITLEordb1.CODE <> db2.CODEordb1.ACTIVE <> db2.ACTIVEif any of the criteria in the where clause is met, I need to create a log file and email it. I'd like to this is using SSIS, however, how can I have a return value of [true] returned in the where statement if something meant, plus the values?so for exampleif db1.organization <> db2.orgranization, I want the values from both databases, return true so I can create a log file, like I mentioned I'd like to do this using SSIS, but if I can't I'll be doing this in a stored procedure that will be kicked off nightlyAm I even going down the right direction with comparing values in the tables?

Incrementing code to add code

Posted: 19 Mar 2013 05:25 PM PDT

Hi,I am trying to join the table to itself many times on a condition. Is there a way to automate number of joins based on count of something from another table?Any help appreciable!Thanks,Akber.

How to read XML file with multiple children and load it in SQL Server

Posted: 19 Mar 2013 08:31 PM PDT

I have an XML file in the below given format. How can I import it into SQL Server using OPENXML or some other method ?----------------------------------------------------------------------------------------------------------------------<?xml version="1.0" encoding="utf-8"?><SRCL> <list> <AOSRC xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <SRC> <SRCNm> <string>Orders.xml_</string> </SRCNm> <SRCHsh> <string>KA-ORDRS-21-Jan-2013</string> </SRCHsh> <SRCRslts> <XSDOS /> </SRCRslts> </SRC> <SRC> <SRCNm> <string>OrderDetails.bin_</string> </SRCNm> <SRCHsh> <string>KJ-18-Mar-2013-WEGraded</string> </SRCHsh> <SRCRslts> <XSDOS> <item> <FieldNm> <string>WarehouseEntryTS</string> </FieldNm> <value> <SRlt> <name> <string>WearhouseEntryTS</string> </name> <value> <string>25-Jul-12 8:24:35 PM</string> </value> <weight> <int>2</int> </weight> </SRlt> </value> </item> <item> <FieldNm> <string>Warehouse Type</string> </FieldNm> <value> <SRlt> <name> <string>Closed and Roofed</string> </name> <value> <string>Type A</string> </value> <weight> <int>143</int> </weight> </SRlt> </value> </item> <item> <FieldNm> <string>DLCR</string> </FieldNm> <value> <SRlt> <name> <string>DLCR</string> </name> <value> <string>00008540</string> </value> <weight> <int>1</int> </weight> </SRlt> </value> </item> <item> <FieldNm> <string>CandF_Section</string> </FieldNm> <value> <SRlt> <name> <string>Forwarding Sections</string> </name> <value> <string>PEandPaperCorrugated</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>PackingAndMovement</string> </FieldNm> <value> <SRlt> <name> <string>PackingAndMovement</string> </name> <value> <string>5 X 6 Feet , With godowns</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> </XSDOS> </SRCRslts> </SRC> <SRC> <SRCNm> <string>PendingOrders.bin_</string> </SRCNm> <SRCHsh> <string>KA-UE-04-Feb-2013</string> </SRCHsh> <SRCRslts> <XSDOS> <item> <FieldNm> <string>WearhouseEntryTS</string> </FieldNm> <value> <SRlt> <name> <string>WearhouseEntryTS</string> </name> <value> <string>25-Jan-13 8:10:15 PM</string> </value> <weight> <int>2</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>Warehouse Type</string> </FieldNm> <value> <SRlt> <name> <string>Warehouse Type</string> </name> <value> <string>v8.0</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>DLCR</string> </FieldNm> <value> <SRlt> <name> <string>DLCR</string> </name> <value> <string>00008540</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>Forwarding Sections</string> </FieldNm> <value> <SRlt> <name> <string>Forwarding Sections</string> </name> <value> <string>ElectricalCarriage</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>PackingAndMovement</string> </FieldNm> <value> <SRlt> <name> <string>PackingAndMovement</string> </name> <value> <string>5 X 6 Feet , Without godowns</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>QuantityTransfer</string> </FieldNm> <value> <SRlt> <name> <string>QuantityTransfer</string> </name> <value> <string>yes</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>StoreID</string> </FieldNm> <value> <SRlt> <name> <string>StoreID</string> </name> <value> <string>Footware</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>Sodexo</string> </FieldNm> <value> <SRlt> <name> <string>Sodexo</string> </name> <value> <string>Sodexo</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>AC Head</string> </FieldNm> <value> <SRlt> <name> <string>AC Head</string> </name> <value> <string>1</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> </XSDOS> </SRCRslts> </SRC> </AOSRC> </list></SRCL>

asp with sql server 2000?

Posted: 19 Mar 2013 04:42 PM PDT

hai friends, I m currently using sql server 2000 DB for my asp applicationmy table iscreate table ttr(startdate datetime,from_loctn varchar(100),to_location varchar(100),Modeoftravel nvarchar(100),seattype nvarchar(100))Modeoftravel and seattype is dropdownbox. if im choose it ll store value only not name(ex:insert into ttr('20-03-2013','abc','dbn','train','IIAC')but showing my table as modeoftravel value is 1 seattype value 1 how to modify

SP Help

Posted: 19 Mar 2013 11:41 AM PDT

Hello

How to get month number like Jan -01, Feb-02 ... Instead of Jan-1, Feb-2

Posted: 19 Mar 2013 09:34 AM PDT

Hi, Need help on get month number from Date field, I am getting month number as 1 for Jan, 2 for FebBut I need 01 for Jan, 02 for Feb..like thisCan you please some one help me outThanksGrace

Design of database table and PK update

Posted: 19 Mar 2013 11:05 AM PDT

We are creating an address database to maintain student address info.I have two tables like this :The address table is a master table that holds all addresses. The studHomeAddress table will hold all students and their address- the addressID comes from the master address table. The primary key of the Address table is Address ID, the PK for studHomeAddress table is studentID and AddressID, we are going to have a screen for user to add or edit for each of the two tables.They can add and edit records, for both of the tables.For the first table Address, I think it is no problem. For the second table, if we want to change the address of the student to another one, then the AddressID will be changed for this record, but it is the PK, the PK for the row will be changed. Is that an OK design?Thanks,

event is occurring between today at 1:00am and tomorrow at 12:59am

Posted: 19 Mar 2013 08:57 AM PDT

Hello,I need a query that finds event is occurring between today at 1:00 am and tomorrow at 12:59 am. I have 2 columns.event_date datatype =date and event_time datatype= timeThis query gives me,SELECT CONVERT(datetime, CONVERT(varchar, DATEADD(day, 0, GETDATE()), 102))--2013-03-19 00:00:00.000 but instead of 0.00 I need my time whihc is 1:00 AM and 12:59 AM of next day.

Substring with IF or CASE statement

Posted: 19 Mar 2013 02:15 AM PDT

Hi,I am trying to import into SQL Server 2008 R2 a list of strings as follows:=KET+N.207-13-F4001=KET+DG014-13-F4011=KET+RE002-36-MV009I need to split the strings so they are inserted into separate fields. One of the substring statements is:'SUBSTRING(xlCode; 15; 2) [if the second position is a number, and]'SUBSTRING(xlCode; 15; 1) [if the second position is anything else]AS GroupNoMy experience with T-SQL is just not enough to figure this one out. Can you tell me how the statement should be set up?Thanks in advance for any help.Maarten

How is SQL query processed in this example?

Posted: 19 Mar 2013 04:04 AM PDT

Hi,I have already posted the problem on Stackoverflow. Can you please help?[url=http://stackoverflow.com/questions/15505165/how-is-sql-query-processed-in-this-example]http://stackoverflow.com/questions/15505165/how-is-sql-query-processed-in-this-example[/url]Thanks much in advance!Regards,Nayan
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog