none
Request for Suggestions RRS feed

  • Question

  • Hello,

    Please help....



    In SQL2005...

    I got a table with 100K or more records.


    When user access...reports are coming slow.


    i want to improve the performance with best option avaiable.

    I got id column in table with identity column. This is primary key.

    tbl_data
    id      numeric(18,0)   PK
    name  varchar

    all other columns are varchar and date datatype.

    what are the options i have to improve query performace?

    1. If i add unique key with 2 unique column, it will improve query fetching speed?

    2. what about adding index?...how?

    Please post all your suggestion....
    Monday, February 23, 2009 3:57 PM

All replies

  • Hi Visios,

    The general strategy is to try to create "covering indexes" for frequently run queries, whenever possible.  A "Covering Index" is one that is built on a query's predicates (columns referenced in the joins or the WHERE clause) and that includes any other columns in the SELECT list.  This can often be impractical for queries that return a large number of columns, so in this case you need to do as much as possible to reduce the result set that you're processing, both in terms of it's width (number of columns) and depth (number of rows).  If you can get it down to a few rows (and be guaranteed that it will stay that way), then an index on the predicates is sufficient, as a bookmark lookup won't cause a significant impact.

    If you can provide the CREATE script for the table, as well as the text of the query/queries that you're trying to optimize, we may be able to provide more specific instructions.
    Aaron Alton | thehobt.blogspot.com
    Monday, February 23, 2009 4:02 PM
    Moderator
  • Thanks Aaron,


    CREATE TABLE [dbo].[file_info](
    [id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [fileupload] [varchar](max) NULL,
    [filesize] [varchar](50) NULL,
    [modifydate] [datetime] NULL,
    [filepath] [varchar](max) NULL,
    [filename] [varchar](max) NULL,
    [filelocation] [varchar](max) NULL,
    [uploaddate] [datetime] NULL,
    [servername] [varchar](50) NULL
    ) ON [PRIMARY]


    here filepath is unique

    or filename and filelocation together is uniqe.



    In where i use some time servername.
    where servername = 'srv01'


    Wednesday, February 25, 2009 9:48 PM
  • OK - what about the query?

    Off hand, the data types look overly large for the application.  Do you really need 18 characters in your ID?  Will an integer not suffice?  Filenames in Windows max out @ 256 characters, so the varchar(max) is overkill. 
    Aaron Alton | thehobt.blogspot.com
    Thursday, February 26, 2009 5:17 AM
    Moderator
  • thanks aaron,

    i will implement ur suggection.

    how much a integer in sql can handle?.


    Here is one query
    select id,filesize,modifydate,filepath,filename,filelocation
    from file_info
    where servername = 'srv01'
    Thursday, February 26, 2009 8:35 PM
  • Is there something like

    getting partical records from sql?

    like give me 1-25 reords...

    then give me 25-50 records?  

    how you write a query for this?
    Thursday, February 26, 2009 8:39 PM
  • Have a look here for the capacities of integer datatypes: http://msdn.microsoft.com/en-us/library/ms187745.aspx

    How many rows does the query you posted return?  One?  A few?  A whole bunch?


    Aaron Alton | thehobt.blogspot.com
    Thursday, February 26, 2009 8:40 PM
    Moderator
  • 59K records
    Thursday, February 26, 2009 8:55 PM