« first day (2246 days earlier)   

2:28 AM
Angular 2 is damn awesome
 
 
4 hours later…
5:58 AM
@McNets Yes it seems much slower against 2016 than v.next for me // cc: @JackDouglas
 
 
3 hours later…
8:41 AM
@McNets yup, I noticed that too: I don't know exactly why 2016 is slower. My guess is that it is the database creation step — maybe that's been streamlined in vNext, or maybe it's faster on Linux — I'll find out when I add a Windows vNext VM.
 
Morning @JackDouglas, well it is quite insignificant.
at least for small scripts
 
I think it is a constant overhead, not dependant on the script size, is that wrong?
 
@JackDouglas No, no. It woks fine. There was just a feeling.
 
I used /SQLSVCINSTANTFILEINIT="True" when installing, though I can't find a way of verifying it actualy worked
 
There is some trace flag trick @JackDouglas
ha, and in 2016 it tells you in the error log by default
 
8:56 AM
Thanks got it — it is turned on. Also CREATE DATABASE is quick from sqlcmd
must be my VB code that is slow
shame, I was hoping I'd never have to look at it again
 
9:23 AM
yes, that's my best guess at the moment, I may need to convert my VB.NET code to C#. Not sure when that will happen though :)
 
@JackDouglas Is the code viewable somewhere?
 
@TomV I haven't uploaded it to GitHub yet, but here is a gist: gist.github.com/jackdouglas/31f126a82c32652d35add06a77938adc
 
@JackDouglas I find it hard to believe VB code could ever be slow.
 
47
A: Is C# code faster than Visual Basic.NET code?

Nick BerardiThat is a myth. They compile down to the same CLR. However the compiler for the same routine may come out slightly differently in the CLR. So for certain routines some may be slightly better like (0.0000001%) faster in C# and vice versa for VB.NET, but they are both running off the same common...

 
@JackDouglas Yeah, if you decompile the intermediate language you can decompile it to c# or vb.NET without issue
you could "rewrite" your code by downloading JustDecompile and have it generate c# from your assembly
 
9:31 AM
"People who like Postgres and MySQL tend to vote down answers that mention SQL Server and Oracle, people who like Waterfall tend to down vote answers that mention Agile and so forth.": Utter nonsense. I may like Postgres and SQL Server, and dislike MySQL but I never base my voting on my likes for a product. There is zero evidence that users vote the way you claim. — ypercubeᵀᴹ 11 hours ago
 
There doesn't seem to be anything obvious in that code that would be slow. Have you confirmed that it is the create database executenonquery that is actually responsible?
 
@PaulWhite I've confirmed that it isn't :S
 
Might be worth tracing (Profiler etc.) exactly what's going on during execution vs. the sqlcmd option.
@JackDouglas Ah.
 
Or it could be the opening/closing of the connections that's improved in vnext
 
9:48 AM
For a simple select 1 foo, the database creation and destruction (which includes two connections to the DB) takes 0.5s our of about 3.5s total
but just adding in the user connection and closing it again adds another 3s, wierd
connecting/disconnecting several times as the new user doesn't add further overhead. Perhaps first time connection by a newly created user takes longer in 2016 than vNext?
 
I seriously doubt that's it. Isn't your infrastructure very different between 2016 and v.next?
 
yes, very
but connecting as sa is fast on 2016, and as newly created user is slow (on same instance)
 
@dezso My guess is that he read my answer in his other question (in our meta) about the mechanics, and the only thing he (mis)understood is the "Someone read your answer and didn't like it".
 
so even ignoring vNext/Linux, that is still weird
 
Since you're connecting with a new user every time there is no chance of connection pooling
but still, 3s sounds a bit much
 
9:58 AM
@ypercubeᵀᴹ whatever, I'm afraid we will never understand the guy
on a funny side note, someone mentioned Perl there as a bad example, and I just stumbled upon one: dba.stackexchange.com/a/73896/6219
 
@TomV I think I could fix the problem by maintaining a pool of pre-created users/databases, but it feels like a lot of extra complexity — I'll definitely wait until I've seen vNext on Windows
 
@JackDouglas So the connection.Open() for the user connection is definitely the slow part? Have you checked what's going on via Profiler at that time?
 
@PaulWhite I'll have to find out what profiler is first ;)
 
@JackDouglas Ah. You're running Express aren't you.
 
yes, and no GUI, no SSMS
but I could possibly install SSMS elsewhere and get it to connect. No point if profiler doesn't work on Express though, is that what you are saying?
 
10:04 AM
@dezso I didn't see it as "Perl a negative example" but perhaps. Anyway, that guy's comments seem good judgement. And he can't see the deleted content at dba.se (answer and comments).
 
@ypercubeᵀᴹ it could have been whichever technology that is different from the one the OP asks about
 
@JackDouglas You could indeed do that. But before you do, can you confirm that the newly created databases are all created with AUTO_CLOSE set ON? I believe that is the default for Express, and may explain the slowness. Consider ALTER DATABASE xxx SET AUTO_CLOSE OFF; immediately after/as part of creating the database.
Depending on how slow the db close/reopen process is on your setup that might be it. Databases unused for a while could be set back to AUTO_CLOSE=ON to conserve resources if that makes sense for you.
Your v.next won't be Express of course.
 
no that is true
 
10:21 AM
You could also change the setting of AUTO_CLOSE on model, which is the template for new databases. I think that would work, but test to be sure.
 
whats wrong with this syntax:
CREATE DATABASE BLAH WITH AUTO_CLOSE OFF
"Incorrect syntax near 'auto_close'."
 
Perhaps AUTO_CLOSE is not supported as a WITH option.
 
ah, OK
@PaulWhite setting it to OFF doesn't change the timings noticeably
 
Ok so it's not that then :)
@JackDouglas Try the test with the Initial Catalog set to master (or tempdb).
@JackDouglas Profiler works fine with Express, it just doesn't come as part of that Edition.
Depending how things work, you might be able to get connection pooling by connecting to master/tempdb always, then immediately changing context to the user database via USE.
 
@PaulWhite Wouldn't that need the username/password combination to be the same too?
i.e. you need the exact same connection string
 
10:32 AM
@TomV Ah yeah :(
EXECUTE AS LOGIN = 'login' WITH NO REVERT; maybe. But that's getting a bit silly.
 
I believe the first time you connect to a new db it has to build the connection pool too
 
I still think tracing the login/connection process & server messages at the time might be revealing.
 
I'm sure you are right
 
It could be a hundred different things.
Good job this is chat or we'd have to close Jack as off-topic :)
 
10:39 AM
Tip of the iceberg?
 
Are the 'computed' column headers a driver thing?
 
Possibly. I thought you were adding those manually. I've never seen 'computed' on computed columns.
 
maybe it's a quirk of FreeTDS
 
In SSMS it's always (No column name) for me (which I think SSMS is adding).
 
with curly brackets?
 
10:47 AM
Round brackets, sorry
 
Yup
 
if that's what SSMS users are used to, I think it's the sensible option for DBFiddle too
 
Fair enough
 

« first day (2246 days earlier)