|
Hey Guys,
I am trying to enhance queries that uses linked server (both servers aare SQL Server) and i am looking for fresh Ideas. I am working with a lot of data.
- I tried to copy data to a temp table and then use the temp table (with appropriate indexs) instead of querying the table in the remote server directly
- using OpenQuery - bad in loops
Would setting up views be a good idea? Any thoughts?
|
|
|
|
|
The most imprtant goal you have is that you don't fetch all the data through linked server and then perform joins, where clauses etc. Try to fetch only the data you need. This may require views and stored procs on both sides that help you eliminate the unnecessary rows as early as possible.
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Thanks for your reply. If we have say 1 remote table that we are working with, I was thinking of creating a view of this table on the local server and then just query the view. But I am not sure if this will enhane the perfromance or not.
|
|
|
|
|
It's quite impossible to generally say if it helps or not. If you can write the view so that it restricts rows on target, it will help. What I'm after is for example something like this:
On local server
- SELECT * FROM View WHERE ... (returns 100 rows)
- view on local server fetches all rows from linked server based on a view on linked server
on linked server
- a view fetches only those rows that are potentially needed on the other side (let's say 1000 rows)
- the table on linked server has for example on million rows
So in the case above, there's only need to transfer 1000 rows (or less, depending on the view and the conditions in the select statement) through the link instead of million rows. This will greately speed up the operation.
Try using Query Analyzer (execution plan) to investigate what actually happens. This will give you a good idea about the bottleneck. If you cannot see operations performed on the linked server side via execution plan, you can bring all the data to the same database, investigate it and when row elimination is done as early as possible, implement the solution on both sides.
Hope this helps,
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Thanks Mika, you were of great help. Thats exactly what I will try to do.
|
|
|
|
|
No problem 
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I often use table-valued functions rather than views because they allow parameters for more fine-grained filtering of rows.
|
|
|
|
|
We are currently using SQLServer 2000 and SQLServer 2005 with Microsoft Exchange as the mail server.
However, by year end, we must switch to the new corporate standard of Lotus Notes (do not know the version).
Assuming it IS possible to configure SQLServer to use Lotus Notes, can anyone provide insight and/or steps to configure this?
And, yes, I have searched for this; just asking for some advice from the trenches.
Any help is appreciated,
Tim
|
|
|
|
|
Sorry, no useful advice just sympathy, at a guess your company has just been taken over and had Notes imposed from above, sorry.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
HI,
Having the following issue and would really appreciate any help/guidence..
We have one SSIS package it will print an html page located in specific location according to the printer name getting from the table. This is working fine and printing to the installed printer on the server.
When we have scheduled a job , which executes the SSIS package it was printing fine in one server and which is 32 bit machine and not printing in 64 bit machine.
In more detail, the job is executing fine and showing the steps success. The log file showing the status that send to the printer. but in 64 bit machine it is not giving print out..No issues with the printer..
Suspecting some permission issue with sql agent on that machine..
Please help me to fix it out as it is eating my head!
Thanks,
Rahul.
SoftwareDeveloper(.NET)
|
|
|
|
|
If the printer is not connected directly to the server, but is a ntwork printer, check that the account you are using for agent has access to the printer in the domain. For example the account isn't local system, but a domain account and has sufficient privileges on domain level.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi Mika,
Thanks for the reply.Appreciating.
The printer is a n/w printer. The account used for the agent has access to the printer. The account used is the admin user for the server.
Another one thing I have noticed is that, when we execute the package as sql utility it is not printing but when we execute the package manually from the folder it is printing..
Will this info help you to provide some more guidence?
Thanks,
Rahul.
SoftwareDeveloper(.NET)
|
|
|
|
|
What is the command you're using for printing (sp's involved etc)?
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
PrintStatus("PrintStatus").Value = 3
flgFindPrinter = False
AttemptCount = -1
While (Not flgFindPrinter And AttemptCount < MaxRetryAttempts("MaxRetryAttempts").Value)
Dim prnDoc As New System.Drawing.Printing.PrintDocument
For Each prn As String In prnDoc.PrinterSettings.InstalledPrinters
If prn.ToLower = PrinterName("PrinterName").Value.ToString.ToLower Then
flgFindPrinter = True
End If
Next
PrintStartTime("PrintStartTime").Value = Date.Now
If flgFindPrinter Then
defaultPrinter = prnDoc.PrinterSettings.PrinterName
Dim wsNetwork As Object
Try
wsNetwork = CreateObject("WScript.Network")
wsNetwork.SetDefaultPrinter(PrinterName("PrinterName").Value)
Dim IE As Object = CreateObject("InternetExplorer.Application")
IE.navigate(FolderPath("AbsoluteFolderPath").Value & DocName("DocName").Value)
While (IE.QueryStatusWB(6) <> 3)
Threading.Thread.Sleep(500)
End While
IE.ExecWB(6, 2, Nothing, Nothing)
Dts.Events.FireInformation(1000, "Print Document", FolderPath("AbsoluteFolderPath").Value & DocName("DocName").Value & " Successfully Printed to " & PrinterName("PrinterName").Value, "", 0, False)
Catch ex As Exception
Dts.Events.FireInformation(2000, "Print Document", ex.Message, "", 0, False)
PrintStatus("PrintStatus").Value = 2
Finally
If wsNetwork IsNot Nothing Then wsNetwork.SetDefaultPrinter(defaultPrinter)
End Try
Else
Dts.Events.FireInformation(2000, "Print Document", PrinterName("PrinterName").Value & " Not Found", "", 0, False)
PrintStatus("PrintStatus").Value = 2
End If
AttemptCount += 1
End While
Dim strLog As String
If flgFindPrinter Then
strLog = FolderPath("AbsoluteFolderPath").Value & DocName("DocName").Value & " Successfully Printed to " & PrinterName("PrinterName").Value
Else
strLog = FolderPath("AbsoluteFolderPath").Value & DocName("DocName").Value & " Failed to Print on " & PrinterName("PrinterName").Value
End If
If AttemptCount <> 0 Then
strLog &= " With Retry Attempts " & AttemptCount
End If
LogDescription("LogDescription").Value = strLog
RetryAttempts("RetryAttempts").Value = AttemptCount
Dts.TaskResult = Dts.Results.Success
SoftwareDeveloper(.NET)
|
|
|
|
|
OOPS Finally, the issue got resolved!! After analysing more into it has been revealed the following was the casue..
The SSIS package is creating IExplorer.exe and when more IExplorer.exe are there in the process then it is getting stuck or wierd. by this the packet send by the job not able to print some time..
This is resolved by using Quit method of the IE object in the program after creation...
Thanks,
Rahul.. 
SoftwareDeveloper(.NET)
|
|
|
|
|
I was just investigating the proc and noticed the same. Another thing is that if I'm reading correctly, your log file will show successfull send if the printer is found even if the printing itself goes to an exception.
But anyway, main thing is you got it solved,
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Yes Mika, you are right as the program will write a sucessfull update to the log even if it fails after finding the printer successfully..
Thaks much for your responses.I appreciate that!
SoftwareDeveloper(.NET)
|
|
|
|
|
No problem 
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
hi
i have sql CE database than contains 1 milion records
i need very quick search.
what is better to do ? Index or Param ?
or someone has any offer for me ? (i write in C#)
thanks alot for any help
|
|
|
|
|
Indexes provide a method of retrieving data very quickly, however you must ask yourself some questions:
1) How many rows will be returned in a typical query ? (10s,100s,1000s) If the query returns many rows it may actually be faster to perform a full-table scan rather than reading through the index. In fact the query optimizer may actually perform a full table scan instead of an index scan, even though the index is present.
2) What is the nature of the data; does it contain many unique values ? For example consider a field called, status (Active / Inactive). This may not be a good choice for an index because it doesn't have very many unique values and retrieving these values would end up causing sequential reads through the index.
3) When designing the index, ideally you would have the index contain items in the select or where clause. For example "select a,b,c from mytable where z = myValue". A good index would contain both Z and A, this way when searching through the index it finds the data that you are looking for (z) and it also has the data that you want, (a). There is no need for the optimizer to go to the main table and retrieve data, everything is available in the index. FAST !
4) The order of the values in the index make a big difference also, but I can't remember a good example.
Lots of things to think about. Post more details about the nature of your query and maybe I can be of more assistance. david
|
|
|
|
|
OK
i have 1 milion records whit 2 fields (barcode(unic) and serial)
i need to scane barcode and show the serial that belong to him.
i need the most fastest way to do that.
what to do ?? index or param ??
help....
thank's alot
(i work on WinCE)
|
|
|
|
|
If these are the only 2 values in your lookup table and the Barcode is unique, then I would make the Barcode a primary key and by default a unique index would be generated by the database engine. There would be no need for an additional index to be created. Regarding parameters, you should always try to make your queries with parameters, this will allow the dbEngine to cache the execution plan for your query, this reduces the amount of CPU required when executing a SQL statement.
For example, "select MySerial from BigTable where Barcode = ?" (not really the syntax, but you get the idea) The dbEngine would parse this SQL statement, choose the best execution plan and store it in it's statement cache so each time it sees this statement it knows how to proceed. This statement will be executed thousands of time with nothing changing but the value for the barcode.
With this type of table and SQL 2005 you should see response times easily under 1/2 second.
|
|
|
|
|
so, index is the best for my project ?
am i right ?
|
|
|
|
|
An index and the use of parameter queries are NOT mutually exclusive, you can (and should) use both techniques for your solution. Yes, an index will give you the performance results you want.
Remember, if your table has the Primary Key defined as the Barcode, then no additional index is required. By defining a primary key the DBEngine will automatically create a unique index for you behind the scenes.
Try it out, you can always create and drop an index to test for any differences in response time.
|
|
|
|
|
Parameter helps you to reuse the SQL statement, index provides faster access to desired rows, but also has it's own downsides.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
