New answers tagged

-1

As i got -1 already and it still will hit my stats will post answer that worked for me: SELECT L1.category_id AS step1, L2.category_id AS step2, L3.category_id AS step3 , L1.title AS title1, L2.title AS title2, L3.title AS title3 FROM nordigen_category AS L1 LEFT JOIN nordigen_category AS L2 ON L2.category_id=L1.parent_id LEFT JOIN nordigen_category AS ...


0

CommandTimeout: Allowing the client to specify a given command's execution time threshold by making it a read/write property is good improvement from the first post of this class, that you did not mention in your "outline of what I have done and why", so I am mentioning it here. Public Property Get CommandTimeout() As Long CommandTimeout = this....


1

great script, it helped me a lot. Thanks. But I've made little modification in [CanRebuildOnline] part. I'm not sure about other versions of SQL, but on SQL 2012 r2 SP4 if any column have set the bad data type, then is not possible to do online rebuild. I've commented useless tables and add the TOP 1 to avoid errors when some table have more than one bad ...


2

"The Properties ParameterNumericScale and ParameterPrecision are used for setting the total number of digits and number of digits to the right of the decimal point in a number respectively. I opted to make these Properties instead of passing them as function parameters to either of GetRecordSet, GetDisconnectedRecordset, ExecuteNonQuery, or ...


3

However, I'm concerned that using subqueries like this may be a bit of a hack and affect performance (especially if I end up adding additional columns that need this information). Whenever you find yourself including a subquery as part of your SELECT list, you're right to question whether or not you're doing the correct thing. These can often suffer from ...


3

I've updated the Fiddle with your initial query and 2 alternatives. This is the initial query plan: Using OUTER APPLY instead of the nested SELECT statements. select created_at, prior_dd_rcvd_status_id, new_dd_rcvd_status_id, prior_dd_status, new_dd_status, filing_id from ES_LOG outer apply ( select dd_doc_status_name as prior_dd_status from ...


4

which one is more efficient Almost surely the unique index will be. Querying back and forth between the server and database is very expensive, and the query that you would have to write to check the table beforehand would do the same thing as the unique constraint - just far less efficiently. The best case is that you'd have to still add an index to the ...


4

By default you will have a numeric userId as well. Using the numeric userId rather than the username will probably perform better and reduce the size of the other tables. The userId will be a foreign key in 3 of the 4 tables and you need to consider the restraints that apply in each of the tables. Naming a picture in the user's profile pictureurl could be ...


4

Your SQL query is never going to be efficient because it has wildcard at the start, and is not Sargable (LIKE N'%015558'). Assuming your database allows it, what you should do in this scenario is to create a PERSISTED computed SQL column which is set to REVERSE(IdentificationNo), and create an index on that column. Combine this with the advice from @RobH, ...


5

Using String.Join makes this kind of string operation a breeze: // DON'T USE THIS - SQL INJECTION string predicates = string.Join(" OR ", batchNos.Select(item => item.Split('/')[1]) .Distinct() .Select(splitItem => $"[TraceableItem].[IdentificationNo] LIKE N'%{splitItem}'")); string queryInsertString = "(" + ...


2

As discussed in the comments, this isn't cryptographically secure. The most obvious reason is that the optimizer may choose to do any number of things. The most likely to be concerning are a change in order of operations, or a change in join methodology. If math.RangeInt does any kind of table access (I assume this gives you a numbers table equivalent) then ...


1

Without knowing exactly what your old code was it is hard to pinpoint where the slowdown came from. That being said, here are some things you're doing in your query that are going to make it slower: Using a UDF prohibits parallel plans. You could throw 1000 CPUs at this problem and it would still only use one of them. If computing this is too complex to do ...


1

I'm going to first address your main question: Like I said, seems to me that if SQL Engine would process the first part(PolicyNumber filtering) that takes 3 seconds, and then do the second part (calculation for those PolicyNumber's) that takes another 3 seconds - that would be awesome. Ultimately, T-SQL is a declarative language, meaning you tell it ...


2

If you're using SELECT TOP 1 1 in an effort to improve performance, know that this isn't always what will happen. In particular, the TOP operator can constrain the ability of the optimizer to pick the best plan. SELECT 1 inside of a EXISTS will do just fine in 99.9% of cases. Additionally, I find it easier to read this: SELECT @isInsert = COUNT( * ) ...


3

A handful of critiques: As mentioned in the comments, this is undefined behavior: SELECT @val = @val + ColumnName FROM AnyTable. As such, you should avoid it. If you need to, use STUFF or STRING_AGG instead When creating a temp table, (n)varchar columns should specify their collation (e.g. COLLATE DATABASE_DEFAULT ) to avoid errors in case server collation ...


1

Python review This could make better use of docstrings and functions; for example, your comment about the Quote class should be a well-formatted docstring instead. Additionally, each of your different comment blocks would be best suited as a function, and then piece them together inside of a __main__ block. I would also do my best to remove any of your ...


3

I don't know anything about mysql, so I'm ignoring that part of your question. For SQL Server, if you're trying to make an insert go faster you're going to want to: Do it in bulk Do it in parallel Make it minimally logged Do it in batches There are some things you can do that will handle all of this for you, which I list below, otherwise you'll have to ...


3

Let me address a possible DRY issue in the query in exchange for possible poorer performance. I'm in particular talking about this bit: /* Generic title */ AND ( Posts.Title LIKE '%please%' OR Posts.Title LIKE '%review%' OR Posts.Title LIKE '%improve%' OR Posts.Title LIKE '%better%' OR Posts.Title LIKE '%how to%'OR Posts.Title ...


Top 50 recent answers are included