none
Abfrage-Performance optimieren RRS feed

 > 

  • Frage

  • Question
    Anmelden
    0
    Anmelden

    Hallo zusammen,

    ich brauche mal einen Tipp!

    Ich habe eine Tabelle mit einer wirklich sehr sehr großen Menge an Zeilen (ca. 14. Mio)! Es gibt einen Primärschlüssel und einen Fremdschlüssel. Wenn ich nun nach einem Schlüssel suche, dauert die Abfrage bereits 5 Sekunden. Wenn ich nach mehreren Schlüsseln suche entsprechend länger! 

    Da frage ich mich an der Stelle, wie schafft man es in einer so großen Tabelle schneller nach den gewünschten Zeilen zu suchen? 

    Freitag, 24. Juni 2016 16:06
    |

Antworten

  • Question
    Anmelden
    0
    Anmelden

    Hallo David,

    14 Mio. ist jetzt nicht gerade wenig aber auch nicht soooo viel.

    Poste bitte das komplette DDL Statement (CREATE TABLE ...) inkl. aller Indizes und Schlüssel.

    Dazu dann bitte dein SQL Statement für die Abfrage.

    Zudem schau dir bitte mal den Ausführungsplan deiner Abfrage an (geht im SSMS bspw. über ein Icon bei einem Abfragefenster, siehe Screenshot)

    Lass dir auch mal die Indexfragmentierung anzeigen.

    SELECT   s.name                            AS SchemaName,
         t.name                            AS TableName,
         i.name                            AS IndexName,
         stat.avg_fragmentation_in_percent AS FragmentationInPercent,
         stat.page_count                   AS PageCount
FROM     sys.dm_db_index_physical_stats( DB_ID(), NULL, NULL, NULL, NULL ) stat
         INNER JOIN sys.tables  t on t.object_id = stat.object_id
         INNER JOIN sys.schemas s on t.schema_id = s.schema_id
         INNER JOIN sys.indexes i ON i.object_id = stat.object_id AND stat.index_id    = i.index_id
WHERE    stat.database_id = DB_ID()
AND      t.name           LIKE '%<Filter>%'
ORDER BY stat.avg_fragmentation_in_percent desc

    (Originalskript kam von http://www.schneider-electric.com/en/faqs/FA234246/)

    <Filter> kannst Du hier mal auf deinen Tabellennamen festlegen, ansonsten stehen da alle Indizes aller Tabellen :)

    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET
    http://www.asp-solutions.de/ - Consulting, Development
    http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community



    Freitag, 24. Juni 2016 16:25
    |
    Moderator

Alle Antworten

  • Question
    Anmelden
    0
    Anmelden

    Hallo David,

    14 Mio. ist jetzt nicht gerade wenig aber auch nicht soooo viel.

    Poste bitte das komplette DDL Statement (CREATE TABLE ...) inkl. aller Indizes und Schlüssel.

    Dazu dann bitte dein SQL Statement für die Abfrage.

    Zudem schau dir bitte mal den Ausführungsplan deiner Abfrage an (geht im SSMS bspw. über ein Icon bei einem Abfragefenster, siehe Screenshot)

    Lass dir auch mal die Indexfragmentierung anzeigen.

    SELECT   s.name                            AS SchemaName,
         t.name                            AS TableName,
         i.name                            AS IndexName,
         stat.avg_fragmentation_in_percent AS FragmentationInPercent,
         stat.page_count                   AS PageCount
FROM     sys.dm_db_index_physical_stats( DB_ID(), NULL, NULL, NULL, NULL ) stat
         INNER JOIN sys.tables  t on t.object_id = stat.object_id
         INNER JOIN sys.schemas s on t.schema_id = s.schema_id
         INNER JOIN sys.indexes i ON i.object_id = stat.object_id AND stat.index_id    = i.index_id
WHERE    stat.database_id = DB_ID()
AND      t.name           LIKE '%<Filter>%'
ORDER BY stat.avg_fragmentation_in_percent desc

    (Originalskript kam von http://www.schneider-electric.com/en/faqs/FA234246/)

    <Filter> kannst Du hier mal auf deinen Tabellennamen festlegen, ansonsten stehen da alle Indizes aller Tabellen :)

    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET
    http://www.asp-solutions.de/ - Consulting, Development
    http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community



    Freitag, 24. Juni 2016 16:25
    |
    Moderator
  • Question
    Anmelden
    1
    Anmelden

    Hallo David,

    ich habe eine Tabelle mit 9,8 Mrd (nicht Mio) Datensätze und wenn ich Daten über die Primarykey (BigInt) selektiere, auch über einen Bereich, dann habe ich Antwortzeiten von ~40 ms (nicht sek). Die Datenbank läuft nicht auf einem High-End Server, sondern auf einem Mini-Rechner für 300 EUR, den ich lediglich mit einer SSD und etwas mehr Hauptspeicher gepimt habe.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Freitag, 24. Juni 2016 16:57
    |
  • Question
    Anmelden
    1
    Anmelden

    Hallo David,

    ergänzend zu den Antworten von Stefan und Olaf ein paar allgemeine Links zum Thema:

    Checkliste für die Analyse langsam ausgeführter Abfragen

    Empfehlungen für die Abfrageoptimierung

    Statistiken

    SQL Server Performance Tips and Guidelines

    Schönen Abend

    Sonntag, 26. Juni 2016 18:44
    |
  • Question
    Anmelden
    0
    Anmelden

    Guten Morgen zusammen,

    danke für die Antworten. Okay, 9,8 Mrd. Zeilen ist ne andere Hausnummer :-) Ich hab bisher einfach noch nie mit so großen Tabellen gearbeitet. 

    Anbei mal der CREATE Code:

    use AOI

create table GlobalInformation(
	GID int not null identity(1,1) primary key,
	BoardID varchar(15) not null default '',
	XMLFileName varchar(50) not null default '',
	OrderNumber varchar(25) not null default '',
	InspectionStart datetime not null,
	InspectionEnd datetime not null,
	RepairStart datetime not null,
	RepairEnd datetime not null,
	NameAOI varchar(25) not null default '',
	InspectionPlanName varchar(25) not null default '',
	Material varchar(25) not null default '',
	Side varchar(10) not null default '',
	Variant varchar(25) not null default '',
	BoardLength int not null default 0,
	BoardWidth int not null default 0,
	OperatorName varchar(25) not null default '',
	Components int not null default 0,
	Defects int not null default 0,
	ComponentsBad int not null default 0,
	TotalSolderJoints int not null default 0,
	Result varchar(10) not null default '',
	VersionSI varchar(25) not null default '',
	VersionRep varchar(25) not null default '',
)

GO

create table PCBInformation(
	PID int not null identity(1,1) primary key,
	GID int not null,
	Barcode varchar(25) not null default '',
	Number int not null default 0,
	PCBID int not null default 0,
	BadBoardStatus int not null default 0,
	FiducialErrors int not null default 0,
	LimitExceeding int not null default 0,
	SideInformation varchar(10) not null default '',
	Result varchar(10) not null default '',

	constraint PCBInformation_GID foreign key (GID) references GlobalInformation(GID)
)

GO

create table ComponentInformation(
	CID int not null identity(1,1) primary key,
	GID int not null,
	Barcode varchar(25) not null default '',
	Window varchar(50) not null default '',
	WinID varchar(50) not null default '',
	WinType varchar(50) not null default '',
	PartNumber varchar(25) not null default '',
	PCBNumber int not null default 0,
	SolderJointNumber int not null default 0,
	AnalysisMode varchar(10) not null default '',
	AnalysisSubMode int not null default 0,
	AnalysisWinNumber int not null default 0,
	AnalysisPictureFileName varchar(25) not null default '',
	ResultType varchar(10) not null default '',
	ResultNumber varchar(10) not null default '',
	ResultErrorDescription varchar(50) not null default '',
	ResultGroupInformation varchar(10) not null default '',
	CADPosX int not null default 0,
	CADPosY int not null default 0,

	constraint ComponentInformation_GID foreign key (GID) references GlobalInformation(GID)
)

    Und das Ergebnis des Scripts von Stefan:

    SchemaName	TableName	IndexName	FragmentationInPercent	PageCount
dbo	PCBInformation	PK__PCBInfor__C5775520CC91A912	0,37988585420305	55543
dbo	GlobalInformation	PK__GlobalIn__C51F0F3EFECCD13D	0,379569271392029	12119
dbo	ComponentInformation	PK__Componen__C1F8DC59B551BBB8	0,37432853191129	230279

    Montag, 27. Juni 2016 06:38
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo David,

    poste bitte noch dein SQL Statement, mit dem Du die Datensätze auslesen willst.

    Welche SQL Server Version setzt Du eigentlich ein? (Bitte die genaue Versionsnummer über SELECT @@VERSION ermitteln)

    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET
    http://www.asp-solutions.de/ - Consulting, Development
    http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community


    Montag, 27. Juni 2016 06:43
    |
    Moderator
  • Question
    Anmelden
    0
    Anmelden

    Hallo Stefan,

    Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
    May 14 2014 18:34:29 
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

    Als Abfrage:

    SELECT * FROM [AOI].[dbo].[ComponentInformation] where GID in (select GID from GlobalInformation where OrderNumber = '100499728')

    9Sek für knapp 1000 Zeilen :-(

    Als Ergebnis vom Ausführungsplan wird folgendes vorgeschlagen:

    /*
Fehlende Indexdetails von SQLQuery3.sql - VW2K12BDEDEV.AOI (sa (56))
Der Abfrageprozessor schätzt, dass durch das Implementieren des folgenden Indexes die Abfragekosten um 96.2642 % verbessert werden können.
*/

/*
USE [AOI]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[ComponentInformation] ([GID])
INCLUDE ([CID],[Barcode],[Window],[WinID],[WinType],[PartNumber],[PCBNumber],[SolderJointNumber],[AnalysisMode],[AnalysisSubMode],[AnalysisWinNumber],[AnalysisPictureFileName],[ResultType],[ResultNumber],[ResultErrorDescription],[ResultGroupInformation],[CADPosX],[CADPosY])
GO
*/

    Ich vermute also, Index fehlt :-)

    Montag, 27. Juni 2016 07:13
    |
  • Question
    Anmelden
    0
    Anmelden

    Ich hab den fehlenden Index hinzugefügt:

    USE [AOI]
GO
CREATE NONCLUSTERED INDEX [IDX_ComponentInformation]
ON [dbo].[ComponentInformation] ([GID])
INCLUDE ([CID])
GO

    Ergebnis:

    knapp 1000 Zeilen in <1 Sek :-)

    Montag, 27. Juni 2016 07:21
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo David,

    sowas in dieser Richtung hatte ich vermutet:

    SELECT * FROM [AOI].[dbo].[ComponentInformation] where GID in (select GID from GlobalInformation where OrderNumber = '100499728')

    für mal das innere SLECT Statement separat aus. Wie viele Datensätze gibt es in der Tabelle "GlobalInformation"? Gibt es einen Index auf OrderNumber, den der Optimierer verwenden könnte?

    Schau dir bitte auch die anderen Tabellen an, dort ist es ähnlich.

    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET
    http://www.asp-solutions.de/ - Consulting, Development
    http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

    Montag, 27. Juni 2016 07:25
    |
    Moderator