The Heap™ – Consultancy ©® - 2014-10-03 (page 1 of 2)

A: varchar storage and comparisons in SQL Server 2008

Does my answer here make sense? I'm pretty sure I've covered the main bits, but I could be overlooking something.

0

Add a persistent calculated field that contains a CHECKSUM on the 5 fields, and use that to perform the comparisons. The CHECKSUM field will be unique for that specific combination of fields, and is stored as an INT that results in a much easier target for comparisons in a WHERE clause. USE tem...

@Marian classic!

Mark Storey-Smith

1:29 AM

@MaxVernon Tick but HASHBYTES probably safer. Could also skip PERSISTED and just create a unique index on the computed hash column

@MarkStorey-Smith safer in what way? less collisions? (I believe you; just unsure)

Mark Storey-Smith

@MaxVernon aye

@MarkStorey-Smith thanks, Mark. I have used HASHBYTES before for this purpose; I like the "less work" you need to do with CHECKSUM. If you don't mind, I'll add your link to my answer so the OP has some choices. That is unless you'd like to offer an alternative answer?

Mark Storey-Smith

@MaxVernon Go for it.

thanks

1:54 AM

The question was closed as "unclear" since you refuse to state the reasoning behind not using the ideas presented as answers below. All of the answers offer potentially great ways to fulfill your requirements. Allowing more people to try to answer your question, when you can and do say "that won't work for me" without saying why is a waste of other peoples time. — Max Vernon 40 secs ago

Chris Travers

4:35 AM

I think I should propose a law on database performance:
For every factor of ten performance gain a tool gets for the specific case, it tends to lose a factor of ten on the general case. In other words a more specialized tool might be able to process 10x more data in a specific case, but at the cost of processing 1/10th of the baseline in the general case....

Q: T-SQL - OPTION (FAST x) and trace flag 8722

5:58 AM

Perhaps a good candidate to move here?

0

I have searched a long time and still haven't found the answer to my problem. Our Dynamics AX is generating queries with the query hint OPTION (FAST x), which sometimes forces a bad execution plan to be used. The devs say it is by default and is hard to change (has to be fixed on all forms poten...

7:14 AM

Got my vote.

Chris Travers

7:58 AM

heh so I am doing ETL, Couchdb to Posttgres

My current jobs are chugging away processing about 40k couch records per hour :-P

andnow I find that at least 2/3rds of the data in this db is duplicate.

James

9:44 AM

@ChrisTravers Cool

@ChrisTravers Are the JSON documents duplicates of each other? Or the data is duplicate once it's converted to a more relational format?

Chris Travers

Duplicaed once we have it in a relational format.

Couchdb: Web-scale until you want to get your data out haha

bluefeet

@MikaelEriksson moved

@PaulWhite great article you have there, but something worries me.

@Marian What's that?

You're the world's best SQL Server query optimizer guy and you has only a "..laptop (with a single 7200 rpm SATA spinning disk)"? :?

9:54 AM

@Marian Feel free to send me money.

2

Talk to Santa, I only give beers :P.

Funnily enough, I nearly bought a Surface 3 today.

one coworker bought it, he hates it, but he's an Apple guy.. So try it before you buy it, if you can.

I doubt I'll ever be brave enough to buy an Apple and run Windows/SQL Server on it.

bluefeet

@AndriyM I wish I could upvote this comment more than once

There's no Markdown to emphasise the word always enough. Some people simply don't seem to get it. — Andriy M 4 hours ago

9:59 AM

The OPTION FAST question is migrated, for anyone interested to answer: dba.stackexchange.com/questions/78306/…

@PaulWhite You could go Aaron's way, looks like it's a setup he enjoys much.

He has infinite confidence and cash though.

You were the one taking vacations in paradise :-).

This is true :-$

@PaulWhite You can get secondhand W-series thinkpads off ebay quite cheaply now. A W520 like the one I bought for £3,500 two years ago could be obtained nowadays for about 1,000 pesos. Merkin ones use the same KB layout as NZ. Add an Intel 525 or similar mSATA SSD and a S3500 for database work and you'd have a laptop that's O for Awesome.

q.v.

ebay.com/itm/…

10:10 AM

Thanks. That's something I will look at.

@PaulWhite Laptops were about the last thing I bought new. After having done the upgrade work on my thinkpad I'd be pretty confident about buying secondhand and ferkin with them myself. They're a bit more fiddly and proprietary than desktops but certainly not beyond the wit of man.

I tried to disassemble a laptop once.
After 3 hours and more than 50 screws on the table, I realized I hadn't even been half-way through.

I will probably end up just replacing the HDD with an SSD in my existing machine for the time being. The memory slots are already full (8GB!) which seemed like plenty at the time...ha.

@ypercube I found videos on Youtube that described how to take down thinkpads.

4 or 5 screws were left overs during re-assembly ;)

10:14 AM

@PaulWhite W520s will go up to 32GB although mine didn't like it for some reason. I think you need a different memory speed to 32GB.

@ypercube Oh, dear.

@Marian (or other folks) I've got a 240GB Intel 730 surplus to requirements. I got it to upgrade my SO's laptoop but her machine won't recognise the disk. Free to a good home if anyone wants it.

@ConcernedOfTunbridgeWells See? Paul wants one.. send it to NZ.

though sending it there will probably not be a very economical wise issue :).

10:40 AM

@Marian Is it any use to you? I still feel bad about the 320 going tits up on you.

@ConcernedOfTunbridgeWells yeah, I've my own SO to upgrade.

I think I'd probably need 750GB or larger, leaning toward a Samsung 840 EVO at the moment.

@PaulWhite no no no no no. See this.

@PaulWhite I've been considering an intel S3500 (yes, I'm already running out of space). They're down to £500 or so for 800GB ones now.

@Marian Good to know, but you'd think they'd be able to fix it, and Oct 15 isn't that far away.

10:54 AM

@Marian I can post it to you. Can you email me your postal address?

@ConcernedOfTunbridgeWells Interesting. I see the 600GB version for NZD 800.

@PaulWhite at least you can go negotiate a better price now, as it's not fixed yet.

@PaulWhite Judging by the F/X rates I'd guess the 800GB one would be about 1,000 pesos or thereabouts.

@ConcernedOfTunbridgeWells Yeah, 1076.40

@ConcernedOfTunbridgeWells I'll get an email up your way. Or in case you meet with Mark in the meantime.. put it in his pocket.

10:57 AM

@ConcernedOfTunbridgeWells Any particular reason you favour the Intel?

@Marian Is he in London at the moment?

@ConcernedOfTunbridgeWells Yep. Coming back to Bucharest in about 2 weeks methinks.

@PaulWhite S3500s are designed for server ops. Although they use ordinary NAND flash they are designed for consistent response times rather than degrading performance.

Cheers.

@MarkStorey-Smith - fancy a pint sometime in the next few days? I've got something for you to deliver to @Marian.

@PaulWhite This is the spec document from Intel's page

intel.co.uk/content/dam/www/public/us/en/documents/…

According to the blurb they're rated as good for about 500 full drive writes or so.

There's already some overprovisioning built into the drive.

11:08 AM

Oh my word, I get an answer from Paul White himself ! Thank you ! The trace flag doesn't seem supported indeed, that's what I suspected. I agree that using trace flags and plan guides are kind of "last resort" to help SQL Server, and that fixing the source of the problem is much neater. I'll have a chat with the devs, and with your answer to support my case (and a lot of angry end users), it should be OK. Thank you all for your help. — Nelson 2.0 3 mins ago

How embarrassing.

@PaulWhite You could flag it.

3

A: how can i use index in mysql

11:23 AM

Love your work.

Kermit

12:22 PM

sigh

-1

Did you CREATE INDEX by any chance?

12:52 PM

Looking into cardinality estimates on XML queries and what difference it makes using the new CE in SQL Server 2014. Anyone else ... no ?

JNK

@MikaelEriksson I'm interested

I know estimates were pretty awful before with anything beyond a trivial query

DBA.SE - More SQL Server MVP's per capita than the PASS Summit.™ — JNK ♦ 8 secs ago

2

@JNK that has to be tweeted :D

JNK

It might be true

It's probably debatable

but I bet if you looked at traffic for a day for logged in users it would be pretty damn high

yeah, that can be

@JNK They are in some way entirely based on the xPath expressions you use in the query. The xPath is divided into a couple (or more) Table-valued functions that has what looks like a fixed number of estimated rows depending on "somthing". I have seen one case where that estimated value on the function is different for the new CE. And the there is difference in the estimates coming out of the joins (Merge or nested loop). And also a difference filter expression checking for null values.

It's a mess but so far the general rule is that you get more rows estimated in SQL Server 2014 new CE than in the old. And that lead to generating a parallel plan more often than before.

JNK

1:06 PM

That's probably good

I recall some horrific plans I got before

1:22 PM

There is some SQL Server humor in there as well. All the estimates are guess work from the start since there are no statistics for XML columns but you get a Cardinality Estimate warning in the plan when you use the value() function that there is an implicit conversion. Not much anyone can do about that warning.

Q: C# Loop executes for some variables from taken from SQL then fails

2:14 PM

We should never be unemployed. I mean, seriously someone thought this was a good idea or they wouldn't have coded it

-2

I'm new to C# programming and wanted to know what I'm doing wrong here. This script has been working fine for the past 2 weeks but now throws a invocation error message. Can anyone shed any light ? The script runs from my local computer and connects to the database on a server. Thanks Bal publ...

c# sql visual-studio-2010 ssis invocation

Kermit

2:36 PM

i've never seen such bad quality

94% of columns are varchar

Q: Very strange SSIS error. DTS_E_PRIMEOUTPUTFAILED

> so it for some reason doesn't like the number of rows it's importing to be 1158

Until I got to that line, I was cheering this user on

0

I've got an odd one here. I have a package that copies some data from one SQL server database to another, into staging tables. That data is then transformed and copied into live tables. This is all done via an SSIS package run by a SQL job. It has been completely solid for months. This morning ...

sql sql-server ssis

@Kermit They are not 100%. Hope there is.

James

@billinkc I'm confused. They're creating a separate table for each row in a loop?

@James EXACTLY! Well, creating the table for each row and then dropping it once they're done

it's the most amazingly painful RBAR solution a person could have come up with. And dynamically built with strings and subject to SQL Injection attacks

@billinkc How simple and elegant!

2:43 PM

I might have to favorite it just so I can hold it up at presentations of What not to do

They could have created a new table per row and column...

James

The actual exception is probably something like "Surely you must be joking"

Or a new database for every row, column and character... CBAC