July 2, 2008 at 5:56 am
Hi to all.
I tested the performance of the select query of the table which i made indexing.
The duration it took for processing is 78758 (After indexing)
The duration it took for processing is 56729 (Before indexing)
So i deleted the indexing for that table and i tested
The result for the above is
The duration for processing is 50316
By reading few article i came to know that indexing will be good for the reading purpose.
Could any one let me know the reason for it.
July 2, 2008 at 6:04 am
Did you check to see if the index you created was useful for the query?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2008 at 6:16 am
First of all, Thanks for your reply.
My table has lakhs of records. Client will access this data so often. THere is no writing process and only reading Hence i'm testing the index now. Could you please let me know whether i am going in the wrong path.
July 2, 2008 at 6:28 am
Try using the query execution plan to see if the index is used o not.
Without more info on table structure, index structure and query, I can't even begin to guess whether SQL will find the index useful or not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2008 at 4:54 am
Index is getting used, i tested using the query execution plan.
Here do i provide the data structure,index and query.
1, data structure
USE [bulkdata]
GO
/****** Object: Table [dbo].[gpsdata_history1_April] Script Date: 07/03/2008 16:15:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[gpsdata_history1_April](
[SNo] [bigint] NOT NULL,
[REGISTRATIONNO] [varchar](50) NULL,
[UNIT_NO] [varchar](50) NULL,
[LATITUDE] [float] NULL,
[LONGITUDE] [float] NULL,
[ALTITUDE] [numeric](15, 9) NULL,
[SPEED] [numeric](5, 0) NULL,
[GPS_DATETIME] [datetime] NULL,
[ODOMETER] [numeric](12, 2) NULL,
[IGNITION] [char](1) NULL,
[DOOR_SENSOR] [char](1) NULL,
[LOCATION] [varchar](200) NULL,
[GEO_BOUNDARY] [varchar](200) NULL,
[BasestationName] [varchar](50) NULL,
[analog1] [varchar](20) NULL,
[analog2] [varchar](20) NULL,
[logic_state] [varchar](20) NULL,
[gps_valid] [char](1) NULL,
[gps_connected] [char](4) NULL,
[SlNo] [bigint] NULL,
[EngineTime] [nvarchar](50) NULL,
[AContime] [nvarchar](50) NULL,
[SpeedLimitTime] [nvarchar](50) NULL,
[IdleTime] [nvarchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
2) index
USE [bulkdata]
GO
/****** Object: Index [index1] Script Date: 07/03/2008 16:20:58 ******/
CREATE UNIQUE NONCLUSTERED INDEX [index1] ON [dbo].[gpsdata_history1_April]
(
[SNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
3) query
select * from gpsdata_history1_April
could you please provide me a solution
2)
July 3, 2008 at 5:03 am
select * from gpsdata_history1_April
That's going to table scan. It has to. You've asked for all the columns and all the rows, no filter. The only way that query can run is to scan either the heap (if the table does not have a clustered index) or the clustered index (if one exists)
The index you've created will be useful if you're filtering by SNo and returning a small percentage of the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2008 at 5:36 am
once again thanks for your kind reply.
Actually sno is just an identity column. I am going to filter only by using registrationno and i will have many registrationno in this table. If i create non-unique, non-clustered index and filter through registrationo wil it become faster.
July 3, 2008 at 5:44 am
mahesh.kumar (7/3/2008)
If i create non-unique, non-clustered index and filter through registrationo wil it become faster.
Create the index on the registrationno column. It should be useful, depends on the queries though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2008 at 5:47 am
Hearty thanks, i will test and revert you if i have any issues
July 6, 2008 at 10:48 pm
GilaMonster (7/3/2008)
... It should be useful, depends on the queries though.
Absolutely. Next time provide the actual query you are trying to optimize. Depending on what you want to read from the table and what you will be filtering on, a covering index may be just what you are looking for. But that cannot be determined until we know the query -- the complete query.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
July 6, 2008 at 11:10 pm
Hi,
This is the query am using for my application.
Select Registrationno,location,gps_datetime,speed,ignition,analog2 from gpsdata_history where registrationno='ht 34 555' and gps_datetime between '' and ''
Here i have used registrationno as non unique and non clustered index.
Thanks in advance
July 7, 2008 at 12:02 am
Now we're getting somewhere useful.
Aprox how many rows would that query return? How many in the total table?
Ignoring covering for now (will get back to that once I have the answer to the above question) I would suggest a composite nonclustered index on registrationno, gps_datetime (In that order)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2008 at 12:17 am
It will give around 80,000 to 90,000 records. Inserting will also happen once in two days and that records are 100 times larger than the above. Will it be handy to remove the index when the insertion happens?.
July 7, 2008 at 12:18 am
I forgot to mention in the previous reply and it is somewhere around 15,00,000 records in the total table.
July 7, 2008 at 12:41 am
In that case, you need to cover that query for the index to be remotely useful. It's not selective enough otherwise.
CREATE NONCLUSTERED INDEX idx_gpsdatahistory1April_RegistrationNoGpsDate
ON gpsdata_history1_April (Registrationno, gps_datetime)
INCLUDE (location,speed,ignition,analog2)
Just curious, what are you doing with those 80000-90000 records returned? Exported to another table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply