select

• DamianC

  SSCrazy Eights

  Points: 8006

  More actions

  October 15, 2015 at 4:47 am

  #312323

  Hello

  I have a relatively small table (around 400k records; 9 columns, all varchar(255)), that takes a long time to select from and I don't know why

  Tried the following:

  select * from table

  select id from table

  select * from table where id = '1' -- should return 1 record

  select * from table with (nolock)

  select id from table with (nolock)

  select * from table with (nolock) where id = '1' -- should return 1 record

  I am running these on the server

  Any of these statements take at least 2 minutes to execute! (I stopped 'select *' after 15 minutes)

  Also checked for any blocking and updated stats

  No other issues with tables on this database

  Has anybody seen this before?

  Thanks

  - Damian

• DamianC

  SSCrazy Eights

  Points: 8006

  More actions

  October 15, 2015 at 4:52 am

  #1833429

  Separate issue - how do I edit the issue title?

  - Damian

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 15, 2015 at 5:48 am

  #1833445

  DamianC (10/15/2015)

  ---

  Hello

  I have a relatively small table (around 400k records; 9 columns, all varchar(255)), that takes a long time to select from and I don't know why

  Tried the following:

  select * from table

  select id from table

  select * from table where id = '1' -- should return 1 record

  select * from table with (nolock)

  select id from table with (nolock)

  select * from table with (nolock) where id = '1' -- should return 1 record

  I am running these on the server

  Any of these statements take at least 2 minutes to execute! (I stopped 'select *' after 15 minutes)

  Also checked for any blocking and updated stats

  No other issues with tables on this database

  Has anybody seen this before?

  Thanks

  What indexes do you have on the table? Please post the scripts for creating them, also the table script.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• DamianC

  SSCrazy Eights

  Points: 8006

  More actions

  October 15, 2015 at 6:10 am

  #1833452

  Hi

  Just realised there are no indexes on this table so this is what I have:

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  SET ANSI_PADDING ON

  GO

  CREATE TABLE [dbo].[E_DIM_POLICY_HOLDER](

  [POLICY_HOLDER_BKEY] [varchar](255) NULL,

  [POLICY_HOLDER_NAME] [varchar](255) NULL,

  [POLICY_HOLDER_SHORTNAME] [char](255) NULL,

  [POLICY_HOLDER_TYPE] [varchar](255) NULL,

  [POLICY_HOLDER_ADDRESS_LINE_1] [varchar](255) NULL,

  [POLICY_HOLDER_ADDRESS_LINE_2] [varchar](255) NULL,

  [POLICY_HOLDER_ADDRESS_LINE_3] [varchar](255) NULL,

  [POLICY_HOLDER_ADDRESS_LINE_4] [varchar](255) NULL,

  [POLICY_HOLDER_POSTCODE] [varchar](255) NULL

  ) ON [PRIMARY]

  GO

  SET ANSI_PADDING OFF

  GO

  I would at least expect a top 10 request to be pretty instant

  Thanks

  - Damian

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 15, 2015 at 7:13 am

  #1833474

  DamianC (10/15/2015)

  ---

  Hi

  Just realised there are no indexes on this table so this is what I have:

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  SET ANSI_PADDING ON

  GO

  CREATE TABLE [dbo].[E_DIM_POLICY_HOLDER](

  [POLICY_HOLDER_BKEY] [varchar](255) NULL,

  [POLICY_HOLDER_NAME] [varchar](255) NULL,

  [POLICY_HOLDER_SHORTNAME] [char](255) NULL,

  [POLICY_HOLDER_TYPE] [varchar](255) NULL,

  [POLICY_HOLDER_ADDRESS_LINE_1] [varchar](255) NULL,

  [POLICY_HOLDER_ADDRESS_LINE_2] [varchar](255) NULL,

  [POLICY_HOLDER_ADDRESS_LINE_3] [varchar](255) NULL,

  [POLICY_HOLDER_ADDRESS_LINE_4] [varchar](255) NULL,

  [POLICY_HOLDER_POSTCODE] [varchar](255) NULL

  ) ON [PRIMARY]

  GO

  SET ANSI_PADDING OFF

  GO

  I would at least expect a top 10 request to be pretty instant

  Thanks

  Put a clustered index on it, unique if data (and business rules) permit. Then run your tests again.

  Change the datatype of your columns from VARCHAR(255) to whatever is most appropriate. Your table is likely to end up a little smaller and filters / joins will be more predictable. Your table is called E_DIM_POLICY_HOLDER - you don't have to repeat the table name in every column name. It helps nothing and adds junk characters to your queries.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• DamianC

  SSCrazy Eights

  Points: 8006

  More actions

  October 16, 2015 at 3:26 am

  #1833715

  Thanks, a clustered index worked a little quicker although not as fast as I would expect

  I do have a number of tables that are much larger and structured in a similar way (varchar 255 etc.) that return pretty much instant results

  Regarding the suggested amendments

  Yes, I will look at this

  - Damian