database - SQL - Optimizing complex paged search querys -

-

I am currently developing an archived procedure for a complex search in a large database because there are several thousand entries, which Can come back i want to use paging Although it is working, I think it is very slow I have read many questions and articles about the receipt of SQL queries and to optimize the performance. But most 'optimization' was only useful for very basic requests such as 'Give items 20-30 to table 20'.

Since our world is not so simple and I want to ask more complex questions, get some help from customizing the following query: 

  create process [SearchItems] @ SAttr1 BIT = 0, @ SAttr2 BIT = 0, @ SAttr3 BIT = 0, @ flag1 BIT = 0, @ flag2 BIT = 0, @ param1 VARCHAR (20), @PARAM2 VARCHAR (10), @SkipCount BIGINT, @TakeCount BIGINT, @SearchStringsNVARCHAR (1000) Announcement @SearchStringsT as table (in Val NVARCHAR (30)), select @SearchStringsT * from dbo.Split (@SearchStrings, ','); Include Table1.ID = Table2.FK1 interiors on table 2 as the ResultTable (Select Table1 with *, ROW_NUMBER (greater than Table1.ID ASC order) [! ROWNUM!] Join Table1 from within Table1.ID = Table2.FK1 Insider Table3.id = Table3 on Table3.FK2 Interiors Table3.XX = Table4.FKX Where Table1.X1 = @ Parameters 1 and (@ flag1 = 0 or Table1.X2 = 1) and (@ flag2 = 0 or Table2.x4 = @ Parameter2) and include (@ Flag3 Table4 = 0 or current (from select * to table 5.ID = table 3.x1)) and ((@ set1 = 0 or current (select * FROM @SearchStringsT Where Table 1.x like Val)) or (@ 2 = 0 or select (select * @SearchStringsT from where Table 2.X1 is valid as well)) or (@ SAttr3 = 0 or exists (select @SearchStringsT *, where the value like Table3.X1)) or (@ SAttr4 = 0 or exists (Choose from @SearchStringsT * WHERE Table4.X1 like Val)) as a result from SELECT TOP (@TakeCount) * where between [! ROWNUM!] (@SepackCount + 1) and (@SepakCount + TechCount) Returns

@SsetR parameters to specify a field or not to specify a bit more parameters, @flag parameters are changing based on / off some Boolean A The inquiry is done, @SkipCount and @TakeCount are used for paging, @ search string is a comma-separated list of search keywords, which already include wild cards.

I hope someone can help me, because in this table a search in a database with 20,000 entries lasts up to 800ms and increasing application with entry count to tackle more than 100 entries Is required.

I thank you very much for every help. I agree with Tom H that this may be a case where dynamic SQL is the best (and I'm a girl of a stored proc, so I do not say so many times), it may be that there is no good indexing on your table. Are all possible search fields indexed? Are all the FK indexed?

I mean that 20,000 is a small, small table and there are also 100,000, so it really looks like you have not yet indexed.

Check your execution to see if the indexes are being used.


Comments

Post a Comment

Popular posts from this blog

windows - Heroku throws SQLITE3 Read only exception -

-
After I deploy the app to Heroku, I have to run migration scripts and get this error message ... ITES \ padrino \ prophetmargin & gt; Heroku Rack AR: Rack mutated! SQLite3 :: ReadOnlyException: only one try to write database to read: CREATE TABLE "schema_migrations" ( "version" varchar (255) NOT NULL) /disk1/home/slugs/215264_925fd2c_65a3/mnt/.bundle/gems/gems/ padrino-core- is 0.9.11 / lib / padrino-core / cli / rake.rb: 9: in `init ' how can this be? I also tried to run Heroku dbpush SQLite: //db/my-db.db and that did not work Horoku sqlite3 but using postgres does not do. I'm not sure why you are receiving this error, however, as I have used sqlite3 in development and when pushing it into its travel, they do some magic which migrates to postgrads. I'm absolutely sure how Heroku does 'backwash' this database but it seems that this is happening to you because it's the SQLite db file which is obviously due to Heroku's
Read more

lex - Building a lexical Analyzer in Java -

-
I am currently learning lexical analysis in compiler design. To know how a lexical analyzer actually works, I am trying to build myself. I am planning to make it in Java The input to the lexical analyzer is a .tex file that is of the following format. \ section {script} {intro} \ section {Scope} arbitrary text \ section {relevance} uncontrolled text \ subdivision (profit) arbitrary text \ subsubsection {in real life} \ subdivision {Ingredient} \ end {document} The output of the lecture may be a table of contents with a page number in another file. 1 Introduction 1 1.1 Scope 1 1.2 Relevance 2 1.2.1 Benefits 2 1.2.1.1 Real life 2 1.2.2 loss 3 I hope that This problem is within the scope of Language Analysis Read the .exe file and check '\' and continue to check whether it is actually on search In the sectioning command is set to indicate whether or not a flag variable is the type of sectioning. I hope the above approach will work for the construction of Lexa
Read more

python - rename keys in a dictionary -

-
I want to change the names of keys in a dictionary which are ints, and I need them so that the inputs with key zero They can sort correctly. For example, my keys are like this: '1', '101', '11' and I may need them: '001', '101', '011' What is doing, but I know that there is a better way tmpDict = {} in the ADC for the old: t MpDict ['% 04d'% int (olden)] = addict [oldkey] New controlled = tmpDict You are going wrong about this if you want to draw entries on a line in a specific way, then you have to sort on the extraction. for sorted (D, key = int): print '% s:% R'% (k, d [k]) < / Div>
Read more