none
Performance issue RRS feed

  • Question

  • Hi,
      I don't if this is correct forum to post. correct me if wrong.
      I have been work with Oracle for more than 5 years but I am new to SQL Server. I download and install SQL Server 2005 Express, create some table, when I run a SELECT statement in my C# code, it took more than 1 second. just a simple SELECT SOME FIELDs FROM TABLE, there are about 15,000 rows return.
      first I thought problem on database so I add index to almost everywhere, no improve. What's the problem? What I'm missing? I really appreaciate your help.

    connection string:
    "Data Source=RN-0D2F1C9E0xxx\SQLEXPRESS;Initial Catalog=DB1;User Id=sqlxxx;Password=sqlxxx;";

    select function:
            public SqlDataReader Select(string SQLSelectString)
            {
                Trace.WriteLineIf(RNMain.rnlsaSwitch.TraceInfo, DDebug.DebugInfo(string.Format("Function:RNSQL.Select(SqlDataReader)")));

                if (SQLSelectString == null || SQLSelectString.Length == 0)
                    throw new ArgumentException("RNSQL.Select.SQLSelectString");

                Connect();

                SqlCommand cmd = new SqlCommand(SQLSelectString);
                cmd.Connection = conn;

                SqlDataReader rdr = cmd.ExecuteReader();

                Trace.WriteLineIf(RNMain.rnlsaSwitch.TraceInfo, DDebug.DebugInfo(string.Format("Function:RNSQL.Select(SqlDataReader)")));

                return rdr;
            }

    here is table
     CREATE TABLE [dbo].[ASG_LOT] (
        [LOT_ID] [int] NOT NULL IDENTITY(0, 1),
        [LOT_NUMBER] [varchar](10) NOT NULL,
        [MKG_LOT_NO] [varchar](100) NOT NULL,
        [DRIVERWAY_SIDE] [tinyint] NOT NULL DEFAULT ((0)),
        [LOT_GRADING] [varchar] (20) NULL,
        [UPGRADE_REQ] [varchar] (20) NULL,      
        [ZONE] [varchar](10) NOT NULL,
        [PROJECT_ID] [varchar](10) NOT NULL,
        [LOCK] [tinyint] NOT NULL DEFAULT ((0)),   
        [STATUS] [tinyint] NOT NULL DEFAULT ((0))
    CONSTRAINT [PK_ASG_LOT] PRIMARY KEY CLUSTERED
    (
        [LOT_ID] ASC
    ) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]   
    ) ON [PRIMARY]


    Wednesday, May 7, 2008 8:37 PM

All replies

  • 15 000 rows is not small amount. How much times does it take same query to run from management studio?
    Wednesday, May 7, 2008 9:46 PM
  • Hi Giorgi:
      Thank you for reply.
      It took no time (00:00:00) in SQL Server Management Studio. Maybe its my database design problem.
      actually it isn't a table, it's a view with 4 table join together, 1 cross table joint 2 other table and one child table. What's the best way to design this table?

    here is the cross table:
    CREATE TABLE [dbo].[ASG_SITING] (
        [LOT_ID] [int] NOT NULL ,
        [TEMPLATE_ID] [int] NOT NULL ,
      ...
        [MODIFIED_USER] [int] NOT NULL DEFAULT ((0))
        [MODIFIED_DATE] [datetime] NOT NULL DEFAULT ((0))
    )

    2 other table:

    CREATE TABLE [dbo].[ASG_LOT] (
        [LOT_ID] [int] NOT NULL IDENTITY(0, 1),
        [LOT_NUMBER] [varchar](10) NOT NULL,
      ...
        [STATUS] [tinyint] NOT NULL DEFAULT ((0))
    CONSTRAINT [PK_ASG_LOT] PRIMARY KEY CLUSTERED
    (
        [LOT_ID] ASC
    ) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]   
    ) ON [PRIMARY]


    and

    CREATE TABLE [dbo].[ASG_TEMPLATE] (
        [TEMPLATE_ID] [int] NOT NULL IDENTITY(0, 1), 
        [MODEL_ID] [int] NOT NULL, 
     ...

    CONSTRAINT [PK_ASG_TEMPLATE] PRIMARY KEY CLUSTERED
    (
        [TEMPLATE_ID] ASC
    ) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]   
    ) ON [PRIMARY]

    and one child table
    CREATE TABLE [dbo].[ASG_MODEL] (
        [MODEL_ID] [int] NOT NULL IDENTITY(0, 1), 
        [MODEL_NUMBER] [varchar](20) NOT NULL ,
    ...
    CONSTRAINT [PK_ASG_MODEL] PRIMARY KEY CLUSTERED
    (
        [MODEL_ID] ASC
    ) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]   
    ) ON [PRIMARY]

    Thursday, May 8, 2008 3:57 PM