|
I had asked this question elsewhere and was advised to ask here instead.
I am wondering if anyone knows how internet connection latency and download speed affect query execution time in SQL Server Management Studio. Is that product similar to using a Data Reader instead of a Data Adapter? Is there a flag I can set to make it more like a Data Adapter? I am trying to get better performance on a remote office, especially since Disaster Recovery can be better handled from the headquarters.
I ran a test in SQL Server Management against the same database across the country. One internet connection had 4x the download speed according to speedtest.net, but a larger latency in ms. The difference is that one connection was a slow ethernet connection and the other a faster wireless connection. Same locations, multiple tests with similar results.
Somewhat surprisingly, the connection with the faster download speed took 50% longer to return a recordset in the 5-20 MB range. Is this because querying in SQL Server Management Studio is similar to executing a query with a Data Reader instead of a DataAdapter? If not, what is the issue?
|
|
|
|
|
Hi,
Don't know if SSMS is more like a data reader than a data adapter and I would guess that it depends on how are you using it. For example if you create a query and run it in a query window you'll get results you cannot modify much like with data reader. On the other hand if you open a table for editing then you can modify the results so the functionality is closer to a data adapter.
Concerning the network latency issue, there's one parameter you can define before you connect, that's affecting the speed quite a lot. If you click the options in the connect dialog, you'll find the network packet size which can be modified. If you have a latency issue, I'd guess that you could increase the packet size even to 16'383 bytes which is the maximum (instead of the default 4'096). If you like you can change this default packet size in the instance settings.
|
|
|
|
|
Thanks! I got a 2x to 6x speedup after raising the packet size to 32767. Now the query execution and transportation time is faster with the faster internet connection with slightly higher latency.
|
|
|
|
|
You're welcome, glad it got solved 
|
|
|
|
|
Hi,
I have to create one subscription via Vb.net for one simple report .
Once i try to deploy this subsciprion the system returns this message:
"rsInvalidParameter400Value of the parameter 'Parameters' is not valid.http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsInvalidParameter&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=10.0.2531.0Microsoft SQL Server Reporting Services10.0.2531.0127OsIndependent1040ReportingServicesLibraryValues of parameter 'Parameters' is not valid."
Here is the code i wrote for my report.
Dim RS As New ReportingService2005
RS.Credentials = System.Net.CredentialCache.DefaultCredentials
RS.Url = "HTTP://VIROSQL08/REPORTSERVER/REPORTSERVICE2005.ASMX"
Dim report As String = "/MYREPORTS/TEST_REPORT"
Dim desc As String = "TEST SCHEDULE"
Dim eventType As String = "TimedSubscription"
Dim StartDateSchedule As String = Today.ToString("yyyy-MM-dd")
Dim StartClockSchedule As String = "09:27"
Dim scheduleXml As String = String.Format("<ScheduleDefinition><StartDateTime>{0}T{1}:00</StartDateTime></ScheduleDefinition>", StartDateSchedule, StartClockSchedule)
Dim extensionParams(7) As ParameterValue
extensionParams(0) = New ParameterValue()
extensionParams(0).Name = "TO"
extensionParams(0).Value = "CED@MYCOMPANY.COM"
extensionParams(1) = New ParameterValue()
extensionParams(1).Name = "ReplyTo"
extensionParams(1).Value = "reporting@adventure-works.com"
extensionParams(2) = New ParameterValue()
extensionParams(2).Name = "IncludeReport"
extensionParams(2).Value = "True"
extensionParams(3) = New ParameterValue()
extensionParams(3).Name = "RenderFormat"
extensionParams(3).Value = "EXCEL"
extensionParams(4) = New ParameterValue()
extensionParams(4).Name = "Subject"
extensionParams(4).Value = "@ReportName was executed at @ExecutionTime"
extensionParams(5) = New ParameterValue()
extensionParams(5).Name = "Comment"
extensionParams(5).Value = "TEST SCHEDULE"
extensionParams(6) = New ParameterValue()
extensionParams(6).Name = "IncludeLink"
extensionParams(6).Value = "False"
extensionParams(7) = New ParameterValue()
extensionParams(7).Name = "Priority"
extensionParams(7).Value = "NORMAL"
Dim parameter As New ParameterValue()
parameter.Name = "PARAM1"
parameter.Value = "1"
parameter.Label = "PARAM1"
Dim parameters(1) As ParameterValue
parameters(0) = parameter
Dim matchData As String = scheduleXml
Dim extSettings As New ExtensionSettings()
extSettings.ParameterValues = extensionParams
extSettings.Extension = "Report Server Email"
Try
RS.CreateSubscription(report, extSettings, desc, eventType, matchData, parameters)
Catch e As SoapException
Console.WriteLine(e.Detail.InnerXml.ToString())
End Try
If i drop the parameter from my report and then set the Parameters with the value Nothing the deployment works fine!!!
What's wrong?
Thank you
|
|
|
|
|
Harmless joke or a mean prank
|
|
|
|
|
Hi all.
I'm trying to use an XML datatype to pass around tabular data between functions, and I'm a bit unfamiliar with both this and XQuery syntax, so this is troublesome for me.
I have a piece of code like so:
DECLARE @time_series XML;
SET @time_series =
'<timeSeries>
<ts><date>2010-06-30</date><value>1.235648</value></ts>
<ts><date>2010-07-31</date><value>564654.235648</value></ts>
<ts><date>2010-08-31</date><value>5465465.235648</value></ts>
<ts><date>2010-09-30</date><value>952031.235648</value></ts>
</timeSeries>'
;
DECLARE @tbl_time_series TABLE(
ts_date DATETIME
, ts_value FLOAT
);
INSERT INTO @tbl_time_series
SELECT
TS.item.nodes('/date').value('.', 'datetime') AS tsdate
, TS.item.nodes('/value').value('.', 'float') AS tsvalue
FROM
@time_series.nodes('timeSeries/ts') AS TS(item)
;
"item", by my reasoning, should allow me to select the date node underneath it in the select, but it appears not.
I get this message:
Msg 227, Level 15, State 1, Line 19
"nodes" is not a valid function, property, or field.
All I want is to end up with a table like this;
ts_date ts_value
---------- --------------
2010-06-30 1.235648
2010-07-31 564654.235648
2010-08-31 5465465.235648
2010-09-30 952031.235648
when I try this:
INSERT INTO @tbl_time_series
SELECT
TS.item.query('/date').value('.', 'datetime') AS tsdate
, TS.item.query('/value').value('.', 'float') AS tsvalue
FROM
@time_series.nodes('timeSeries/ts') AS TS(item)
;
See the query in the select statement instead of the node? this runs alright, but I get a result set like this:
1900-01-01 00:00:00.000 0
1900-01-01 00:00:00.000 0
1900-01-01 00:00:00.000 0
1900-01-01 00:00:00.000 0
I think I'm nearly there but my XQuery noobiness is failing me... 
Any suggestions?
|
|
|
|
|
UPDATE
Solved. Here's the final statement.
DECLARE @time_series XML;
SET @time_series =
'<timeSeries>
<ts><date>2010-06-30</date><value>1.235648</value></ts>
<ts><date>2010-07-31</date><value>564654.235648</value></ts>
<ts><date>2010-08-31</date><value>5465465.235648</value></ts>
<ts><date>2010-09-30</date><value>952031.235648</value></ts>
</timeSeries>'
;
DECLARE @tbl_time_series TABLE(
ts_date DATETIME
, ts_value FLOAT
);
INSERT INTO @tbl_time_series
SELECT
TS.item.query('date').value('.', 'datetime') AS tsdate
, TS.item.query('value').value('.', 'float') AS tsvalue
FROM
@time_series.nodes('timeSeries/ts') AS TS(item)
;
SELECT * FROM @tbl_time_series;
Just as I suspected it was my lack of XQuery-fu... d'oh!
Hope someone else finds this useful anyway.
|
|
|
|
|
I seem to be incapable of figuring out how to construct a query to achieve a simple thing. I was given a Microsoft Access table that looks like this: (This is just a simplified example)
RoadID Begin End
----------------------------
01005 0.0 2.3
01005 2.3 4.2
01005 4.2 10.7
01005 12.5 14.4
01005 14.4 16.9
01002 0.0 13.2
01002 13.2 16.2
01002 16.2 17.8
01002 17.8 21.2
02003 0.0 5.5
02003 7.2 10.2
02003 10.2 16.3
02003 16.3 27.8
Is it possible to make a query to find all the records that have a gap between it and the records before or after it (for each RoadID). The order of the original records should be sorted by RoadID and Begin. For this example, the output should be:
RoadID Begin End
----------------------------
01005 4.2 10.7
01005 12.5 14.4
02003 0.0 5.5
02003 7.2 10.2
In the above result, the first line has 10.7 as the End, and the second line has 12.5 as the Begin -- meaning that there is a gap, so both records should be shown. Same with the third and fourth lines. This is done for each RoadID, so for example, even though there is a gap between these two records (16.9 <> 0.0), they should not be shown in the result because their RoadID values are different (01005 and 01002):
01005 14.4 16.9
01002 0.0 13.2
I have tried to use some inner join tricks but could not get a thing. I could do this through a program, but I am curious to know if a single SQL query could achieve it. Any help on this is appreciated. Thanks!
|
|
|
|
|
How should the query react to overlaps?
|
|
|
|
|
Sorry I failed on making this point. The original question should be to find gaps and overlaps. Good point! Thanks!
|
|
|
|
|
Ok, this one works with your example: No it doesn't, it'll give you the combined stretches of road with gaps between them
WITH t1 AS (
SELECT r1.RoadID,r1.BEGIN
FROM Roads r1 Left outer join Roads r2
ON r1.RoadID = r2.RoadID
AND r1.BEGIN > r2.BEGIN
AND r1.BEGIN <= r2.END
GROUP BY r1.RoadID,r1.BEGIN
HAVING Count(r2.BEGIN) = 0
)
,t2 AS (
SELECT r3.RoadID,r3.END
FROM Roads r3 Left outer join Roads r4
ON r3.RoadID = r4.RoadID
AND r3.END >= r4.BEGIN
AND r3.END < r4.END
GROUP BY r3.RoadID,r3.END
HAVING Count(r4.BEGIN) = 0
)
,ContiguousStretch AS (
SELECT t1.RoadID,t1.BEGIN,Min(t2.END) AS END
FROM t1 join t2
ON t1.RoadID = t2.RoadID
AND t1.BEGIN <= t2.END
GROUP BY t1.RoadID,t1.BEGIN
)
,Gaps AS (
SELECT RoadID
FROM ContiguousStretch
GROUP BY RoadID
HAVING Count(RoadID) > 1
)
SELECT c.RoadID,BEGIN,END
FROM ContiguousStretch c join gaps g
ON c.RoadID = g.RoadID
ORDER BY c.RoadID,BEGIN
|
|
|
|
|
|
Take two, this one works with both overlaps and gaps and with no contiguous results:
WITH ordered AS(
SELECT RoadID,BEGIN,END,ROW_NUMBER() OVER(PARTITION BY RoadID ORDER BY BEGIN) as rn
FROM roads
)
SELECT o1.RoadID,o1.BEGIN,o1.END
FROM ordered o1,ordered o2
WHERE o1.RoadID = o2.RoadID
AND o1.rn = o2.rn -1
AND o1.END <> o2.BEGIN
UNION
SELECT o2.RoadID,o2.BEGIN,o2.END
FROM ordered o1,ordered o2
WHERE o1.RoadID = o2.RoadID
AND o1.rn = o2.rn -1
AND o1.END <> o2.BEGINIf you want only the gaps or only the overlaps you vill have to change the conditions in o1.END <> o2.BEGIN
|
|
|
|
|
Thanks, Jörgen! BTW, I have been to Sverige (Göteborg, Stockholm, and Malmö) ten years ago. Beautiful places. I liked it there.
|
|
|
|
|
You could try something like the following
SELECT a.*, b.*
FROM TableName a,
TableName b
WHERE a.roadid = b.roadid
AND b.start = (SELECT MIN(c.start)
FROM TableName c
WHERE c.roadid = a.roadid
AND c.Start > a.start)
AND a.End <> b.Start
ORDER BY ...
|
|
|
|
|
I appreciate your help. Thanks!
|
|
|
|
|
I'm at the last stage of a complete overhaul of our build scripts and I'm down to one last issue.
I can't seem to use sqlcmd to execute a create procedure statement that has a raiserror command in it. sqlcmd pre parses out the command and fails without executing. There doesn't seem to be any documented escape that I can figure out.
|
|
|
|
|
Just made a test with this and didn't have any problems. Could it be that you're having problems with for example apostrophes in the script in the RAISERROR statement.
The test script I used was:
create procedure ptest as
begin
raiserror( 'message', 10, 10 );
end;
go
and the command:
sqlcmd -S .\instancename -E -d dbname -i testproc.txt
Could you post the error message and the part of the script that's causing problems. Also have a test drive with the script above to see if it also causes the same problem.
|
|
|
|
|
I simply searched on here for code to finish a much over due uni ap assignment, now where all this hostility comes from I dont really know, mostly on to finish moi ap tell you the code works, apparently no one gets gleeful when finishing an ap that will progress them into their real life,
I dont see moi self as a programmer nor a even an avid enthusiasts in programming thinking I better post something being that I wasnt planning to even join this jest to getthe code
DO NOT FLOOD MOI EMAIL BOX: WITH INSULTS...
direct Q's that I cannot answer directly plese direct them somewhere else
usernameshelby@hotmail.com
|
|
|
|
|
Well shelby this not an insult but an observation. You posted this in a database forum, I presume you want something to do with a keyboard.
You did not actually post a question just a minor rant about how you were treated.
I also assume you have cross posted this, I suggest you read the forum guidelines on how to post a question to get a sensible answer.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It wasnt a rant I dont know what you mean, besides more of a praise report so I dont get an incomplete! I have to complete jest develop five apps that work that isnt jest
I have a problem with you trying to observe someone like Im in an observatory I cannot approve....I dont know anything pertains to guidelines...
|
|
|
|
|
English is obviously not your strongest language because your sentences do not make sense, I suggest you get someone with reasonable english to help you frame your questions.
See the yellow highlighted post at the top of the page, go read that, it will help you ask a valid question.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Shelby. Which country are you really from? We may be able to get somebody from your country with a fuller grasp of English to help form a question that we can answer.
|
|
|
|
|
Smells like a Troll 
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
