@PaulWhite Is this re the "Queries without Good Enough Plan Found". I definitely saw a red -10 earlier on today, at least I thought I did, but really I know I shouldn't worry about these things, and you can't please everyone : )
An "unupvote" isn't the same as a "downvote". So don't confuse them.
A "downvote", is a vote that nets you -2.
An "unupvote", is the removal of an upvote.
In your case, you received an upvote for which you gained +10. Later on, the user removed that vote - thus you lose the +10 that you origi...
@PaulWhite you know you do so much for the site, I want to ask, is there a vision, or did you decide that? You guys are really crafting a repository here I think.
@PaulWhite You and a few of the others (you know who you are) put so much into the site, that I wondered if there as a time when you all decided that, and there was a vision, long-term goal or something, or it's just you're all ardent supporters of the community and no more to it than that. I think that's my question really.
@PaulWhite Just blabbering on really, it's late here : )
Hmm, it's nearly 2am, whether to spend all night rendering a unicorn in the form of ASCII art created by a SQL statement ... or go to bed. Decisions decisions...
@PaulWhite - in my answer on the palindrome question where I show the multi-statement TVF, how can I see that the plan is "bad" - i.e. it executes the function multiple times per row?
in my sample, the plan shows the TVF being "actually" executed 2 times, which equates to once per row in the input table.
morning, @TimStone
how's things in diamond-land?!
Chemical Brothers - Born in the Echos cranked to 11 at 7am is where it's at!
@MaxVernon I don't think it is about a bad plan but about the implicit context switching for each call to the function. That takes time and does not happen for inline TVF's.
@ypercubeᵀᴹ. Hey, I wanted to see if we could pick up where we left off yesterday. We were discussing transactions within functions using postgres. Something like:
I was thinking more along the lines of: CREATE FUNCTION myFunct (IN var text...) RETURNS boolean AS $$ BEGIN TRANSACTION UPDATE table 1..... UPDATE table 2....... UPDATE table 3.......... COMMIT; END; $$ LANGUAGE plpgsql
@unseen_damage As we implied yesterday. WHy not just?:
CREATE FUNCTION myFunct (IN var text...) RETURNS boolean AS $$
BEGIN;
UPDATE table 1.....
UPDATE table 2.......
UPDATE table 3..........
END;
$$ LANGUAGE plpgsql
@MikaelEriksson yah, I've been comparing the inline TVF proposed by @AndriyM compared to my non-inline TVF. Looking at my plan, the details of the TVF are hidden by the "Table Valued Function" operator, whereas his plan just shows the "normal" query operators such as table scan, and nested loop joins, etc.
so the takeaway from a plan-perspective appears to be "if you see a table valued function operator in a plan, that's A Bad Thing™"
@MikaelEriksson yes, looks interesting. Thanks! I'll read it in detail momentarily!
interestingly, comparing execution statistics for my multi-statement TVF vs the inline TVF, the inline TVF is taking a lot longer to run.
not at all what I expected
statistics times:
Multi-Statement: CPU time = 62 ms, elapsed time = 228 ms.
Inline: CPU time = 113288 ms, elapsed time = 113970 ms.
I must be doing something wrong
this is the inline-tvf:
CREATE FUNCTION dbo.InlineIsPalindrome
(
@Word NVARCHAR(500)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
WITH Nums AS
(
SELECT
N = number
FROM
dbo.Numbers
)
SELECT
IsPalindrome =
CASE
WHEN EXISTS
(
SELECT N
FROM Nums
WHERE N <= L / 2
AND SUBSTRING(S, N, 1) <> SUBSTRING(S, 1 + L - N, 1)
)
THEN 0
ELSE 1
END
FROM
(SELECT LTRIM(RTRIM(@Word)), LEN(@Word)) AS v (S, L)
);
GO
select top(20000) c1.name+reverse(c2.name) as name
into #words
from sys.all_columns as c1, sys.all_columns as c2
select *
from #words
cross apply dbo.InlineIsPalindrome(name)
select *
from #words
cross apply dbo.IsPalindrome(name)
And got 140 ms for the inline version and 1600 ms for the multi statement.
@MarkSinkinson the row count comes from a cross join of sys.objects with itself on my Test database. If I do SELECT COUNT(*) FROM #Words it now says 17161, because I added some other objects to the database.
I have an alphanumeric string as input and I want to get two results out of it:
a string where all the numerals are removed
and
an integer that is the sum of all the digits in the input string.
For instance, for this input:
GR35hc7vdH35
I want the following output:
| Col1. | ...
@MDCCL It didn't look awfully unclear to me but I could see how OP's lack of explicitness could be making it difficult for someone else to get what the issue was.
@MaxVernon That's the table variable @t in the msTVF being created, and written to (one row!) for each execution.
If you still have the test rig, try capturing execution plans using Profiler.
Seriously, try it.
You'll get a complete execution plan, inserting one row to the table variable for each row in the #Words table. A complete execution plan for each row.
That is what @MikaelEriksson was alluding to with the 'execution context' remark, though it's worse than just 'switching context' in the sense of evaluating permissions.
The overhead is similar to executing a completely new query for each row.
So when I said it was no better than the scalar function, I meant it was worse.
Instead of executing a query per row, you're now executing a query per row, putting it in a temporary table variable, waiting for the client query plan to read that row from the table variable, dropping it, then repeating for each row. Yikes, right?
I have an alphanumeric string as input and I want to get two results out of it:
a string where all the numerals are removed
and
an integer that is the sum of all the digits in the input string.
For instance, for this input:
GR35hc7vdH35
I want the following output:
| Col1. | ...
You're an absolute lifesaver, thank you so so much! This solution wrote a 140k+ record set to CSV in just a few seconds. I'll post the VB.NET version of the code in a separate comment in case it's helpful to anyone in the future. You're the best!! — Kel14 mins ago
Pretty sure I heard Conor Cunningham say "don't do bitcoin in the database". Don't know why he was so against it, maybe commercially, or just because it's a stupid thing to do.
The question is specifically about using master..spt_values to split strings. I'm sure there are more generic questions about string splitting in SQL Server, which is where I believe this answer should belong. (And then you could just post a link to it in a comment to this question if you wanted to draw people's attention to the new feature.) — Andriy M35 secs ago
@MaxVernon Everyone should trace a multistatement TVF at least once in their lives. They're not always a bad idea, but usually. The valid use cases are rather small, but probably larger than scalar functions as implemented today.
@PaulWhite it seems to enforce to me the value of testing several ways of skinning the cat before deciding on the exact method to use for that particular cat.
@MaxVernon I thought that was primarily about DBA.SE.
Although I'm sure Paul is a generous person and would probably not mind for that statement to be extended to other sites as well, even to SO. Sometimes.
The function 'reverse' is not supported with natively compiled modules. Or the UK government. Oh hang on, no sorry, yes it is supported fully by the UK government.
I'm very familiar with cryptocurrency and databases, and I can tell you it's not a great DB engine at all. Think of it as a first normalized form without any really good built in search capability or indexing as far as the blockchain goes. Basically a excel sheet without any computation capabil...
@MaxVernon Actually, I wouldn't. The number of weird little discrepancies between stats io/profiler/xe/... over the years, I've given up being interested.
Plus palindrome family, status of palindrome, effective date etc. In the future, the status of some particularly long palindromes is in dispute as no computer is able to determine effectively whether or not it is indeed a palindrome. A quantum computer was asked, but it say "maybe".
@MaxVernon Battlelines are drawn as the Association for Rights of Sentient Palindromes square up to the People Against Palindromes organisations. Incumbent president of United States admits a palindrome could win the next election, new religions spring up around messages hidden within palindromes, deep within ...
@MaxVernon I edited your answer with your removed query, making a slight correction (and rolled back to hide it again ;) Not sure if you want that version. But to me it seems like a good one.
A very good one.
If you roll back to revision 3, future readers will think we've been on a (very strange) edit war!
Using mysql 5.6: I'm running a CREATE TABLE foo AS SELECT * FROM bar WHERE x > y, and I did a COUNT on that result set previously, and it came to 227 651 192 rows (3 integer columns and 1 binary(16)). Now it's been running 50 minutes, the foo.idb just stopped growing at "9.7G" and has been stuck on that size for 10 minutes. Any ideas? SHOW PROCESSLIST is showing "Sending data"
An "unupvote" isn't the same as a "downvote". So don't confuse them.
A "downvote", is a vote that nets you -2.
An "unupvote", is the removal of an upvote.
In your case, you received an upvote for which you gained +10. Later on, the user removed that vote - thus you lose the +10 that you origi...
this is the best solution what i found ;-)
I have an alphanumeric string as input and I want to get two results out of it:
a string where all the numerals are removed
and
an integer that is the sum of all the digits in the input string.
For instance, for this input:
GR35hc7vdH35
I want the following output:
| Col1. | ...
I'm very familiar with cryptocurrency and databases, and I can tell you it's not a great DB engine at all. Think of it as a first normalized form without any really good built in search capability or indexing as far as the blockchain goes. Basically a excel sheet without any computation capabil...
