none
Sql Server 2012 Speed Problem

    Question

  • Hello!

    I hope that someone can help me!

    I have a database Tebelle with> 6 million records

    This one table broke for a select out of the database about 8 seconds for an answer ...

    All other tables within the database are running fast ...

    To build the table:

    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [DEHODEH1].[LOG_INFO](
           [LOG_TS] [datetime2](6) NOT NULL DEFAULT (getdate()),
           [LOG_MAN] [char](3) NOT NULL DEFAULT (' '),
           [LOG_KATEGORIE] [char](30) NOT NULL DEFAULT (' '),
           [LOG_NTID] [char](8) NOT NULL DEFAULT (' '),
           [LOG_STATUS] [char](1) NOT NULL DEFAULT (' '),
           [LOG_FILE_ACTION] [char](1) NOT NULL DEFAULT (' '),
           [LOG_UP_INFO] [char](2) NOT NULL DEFAULT (' '),
           [LOG_MQ_TRANSFER] [char](1) NOT NULL DEFAULT (' '),
           [LOG_PROG] [char](8) NOT NULL DEFAULT (' '),
           [LOG_KEY] [char](50) NOT NULL DEFAULT (' '),
           [LOG_DATA] [varchar](3900) NOT NULL DEFAULT (''),
    CONSTRAINT [LOG_TS] PRIMARY KEY CLUSTERED 
    (
           [LOG_TS] ASC,
           [LOG_MAN] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
    )
    
    GO
    
    SET ANSI_PADDING OFF
    GO

    Now the Select:

                select                                                
                   log_ts,                                            
                   log_kategorie,                                     
                   log_ntid,                                          
                   log_key,                                           
                   log_data                                           
                from dehodeh1.dehodeh1.log_info                                
                where log_key       >= '210000009210        '  AND            
                      log_key       <= '210000009210ZZZZZZZZ'  AND            
                      log_kategorie  = 'ADRESSE'                      
                 order by log_ts 

    And the Database Informations:

    SQL Server     11.0.620.0
    MDAC             6.3.9600.1850
    OS                   6.3.9600

    The problem is not the call within the SQL Server but if the call is made externally, eg. via XA Resource

    I suspect that it is the structure of the table?




    Tuesday, July 10, 2018 12:32 PM

All replies

  • Please look at formatting when  you post your question, I have formatted it but removed something liek table structure. Can you add it in the question again.

    Now as per your query a non clustered index on column Log_key which would include log_kategorie should make your query fast


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, July 10, 2018 1:14 PM
    Moderator
  • Sorry for the "format"!

    How should the Select look like?
    Tuesday, July 10, 2018 1:28 PM
  • Sorry for the "format"!

    How should the Select look like?
    It is not the select but the non clustered index which you should be worried about like I said above. Currently CI is on two columns which are not part of predicate. The where clause has column `log_key` and `log_kategorie`. You need to create NCI on Lo key and include log_kategorie. I hope this is clear, if not let me know

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, July 11, 2018 6:37 AM
    Moderator
  • Hi Bernd Riemke,

    Could you please share the query plan of your query?

    According to your description, it looks like that you did not create a non-clustered index on the column log_key and log_kategorie, if it is, it is normal to take 8 second when querying on a 6 million records table.

    After creating the non-clustered index, it will query faster, if it did not use the index, we can also use the WITH (INDEX(****)) to force using.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 11, 2018 6:48 AM
  • I will test it and come back ...

    Best Regards

    Bernd

    Wednesday, July 11, 2018 7:01 AM
  • Many Many Thanks for the Hint!

    I will test ist and come back!

    Best Regards

    Bernd

    Wednesday, July 11, 2018 7:02 AM
  • Can you have an index on (log_kategorie,log_key ) include (log_ts,log_ntid,log_data   )?

    )

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, July 12, 2018 5:04 AM
    Moderator
  • Here is the new Key, but it is slow again...

     
    USE [DEHODEH1]
    GO
     
    /****** Object:  Index [XLOGINF]    Script Date: 7/13/2018 3:33:00 PM ******/
    CREATE UNIQUE NONCLUSTERED INDEX [XLOGINF] ON [DEHODEH1].[LOG_INFO]
    (
           [LOG_TS] ASC,
           [LOG_MAN] 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, FILLFACTOR = 80)
    GO
     
    /*---------------------------------------------------------------------------------------------------*/
     
    USE [DEHODEH1]
    GO
     
    /****** Object:  Index [YLOGINF]    Script Date: 7/13/2018 3:33:06 PM ******/
    CREATE NONCLUSTERED INDEX [YLOGINF] ON [DEHODEH1].[LOG_INFO]
    (
           [LOG_MAN] ASC,
           [LOG_KATEGORIE] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
    GO
     
    /*---------------------------------------------------------------------------------------------------*/
     
    USE [DEHODEH1]
    GO
     
    /****** Object:  Index [ZLOGINF]    Script Date: 7/13/2018 3:33:31 PM ******/
    CREATE NONCLUSTERED INDEX [ZLOGINF] ON [DEHODEH1].[LOG_INFO]
    (
           [LOG_KEY] ASC,
           [LOG_KATEGORIE] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
    GO
     
    

    Have you and idea?

    1 hour 50 minutes ago
  • I only asked you to create index on Log_key and include log_categorie, whats reason for creating other indexes

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    1 hour 18 minutes ago
    Moderator
  • I have just received the settings from the DB Administartor.
    It's about the time behavior that no one can explain.
    Is there anything wrong that explains this timing?
    Now I have written everything from the table what I have ...
    An idea?

    Best Regards

    Bernd

    52 minutes ago