can someone help me turn this 3 querys into one?

SELECT COUNT(*) FROM testes__db WHERE module = %s
UNION ALL
SELECT COUNT(*) FROM result_testes__db a1 INNER JOIN testes__db a2 ON a1.req_id = a2.id and a2.module =%s and a1.test_id =%s
UNION ALL
SELECT COUNT(*) FROM result_testes__db a1 INNER JOIN testes__db a2 ON a1.req_id = a2.id and a2.module =%s and a1.test_id =%s where eval=0

Then add all parameters... You'll save 2 database roundtrip times which is not much. If you want to overdo it, and "eval" is a bool, you could do:

SELECT 0, COUNT(*) FROM testes__db WHERE module = %s
UNION ALL
SELECT eval, COUNT(*) FROM result_testes__db a1 INNER JOIN testes__db a2 ON a1.req_id = a2.id and a2.module =%s and a1.test_id =%s GROUP BY eval

So you get the count for both values of "eval" in one query.

I gather that it would be faster and better than having the 3 select running all the time.

That's not necessarily the case.

First, you should benchmark it. Back when I was doing websites, I added a query log at the bottom of the page showing all queries and their execution time. This was displayed only to a user logged in with admin credentials, of course, otherwise it would be a rather large security hole. This is a nice way to immediately spot slow queries while browsing the site.

You can do this with a wrapper object on the database connection.

Then once you know which queries are actually a problem, you can spend time optimizing them, or doing other things like caching the results.

Our environment is the following:

SQL Server 2019 (15.0.4153.1) + SQL Integration Services (15.0.2000)

We have a DTSX Package that uses a OLE connection manager to connect to an SQL Server to read data from and an ODBC 32 Bit connection manager using the Oracle Instant Client 21 to write the data to.

The package itself is absolutely stupid and simple and just reads data from a view and without any modification writes the data to the target table.

We have to variants of the DTSX Package: the first (and original package which ran on an SQL Server 2012 for years without problems) has a transformation step included which casts the data read from the SQL Server to different types before writing them to the target table. E.g. DT_I4 was cast to DT_NUMERIC. It seems that this step was necessary back then, but can be omitted now.

Now the observation is that the package works perfectly fine when executed directly from Visual Studio (2019). The data is written correctly, here is a simplified example:

Given Name | Last Name
-----------------------
John       |  Doe
William    |  Smith
Claire     |  Anderson
Jane       |  Scott

But when run as a Job via the SQL Server Agent (details below), the result in the target table looks like this:

Given Name | Last Name
-----------------------
William    |  Smit
Willi      |  Smi
Wil        |  Smith
William    |  Sm

I have absolutely no clue how that can be even possible, because in my naive world (which usually does not involve Microsoft products, so I am absolutely not familiar with how SSIS actually works) rows are processed as rows and nothing like this can ever happen.

Not only that the rows are mixed up, the contents of the row are also corrupted and truncated arbitrarily.

Has anyone observed this problem and knows a solution?

The job we created consists of one single step which executes an Integration Service Package. We already tried a) reading the file directly from the file system and b) reading the file from a file storage on the SQL Server where we imported the DTSX. Both yield the same result. Integration Service Catalog is unfortunately not an option for us.

The package runs in the 32 bit environment due to the 32 bit ODBC driver we needed to use (because the Visual Studio 2019 where the package is built is 32 bit software).

The target server version configuration property in the package is set to SQL Server 2019 (was SQL Server 2012 before).

The most irritating things here are a) that the same DTSX Package ran for several years in an SQL Server 2012 without any problems and b) that the Package also runs from Visual Studio without any problems. Only when executed as a Job in the SQL Server, the data is completely corrupt.

More Information:

The data source for the Oracle Database is a system data source which is used by both the SQL Server and Visual Studio when executing. So I can rule out that different ODBC drivers are used.

Credentials for the Database are stored within the package and protection mode is EncryptSensitiveInformationWithPassword. The question in the comment was which permissions the user have on the Oracle Database. The user we use is the schema owner.

The user executing the package, though, is different, since it is the logged in user (local admin) on the machine when executing the package from Visual Studio and on the other hand, the package is executed by the SQL Server Agent and I suspect the SYSTEM User is doing that.

Assuming the result is held by say 4 threads, then, until the result is entirely consumed by the client application, do the threads that hold the data remain locked?

Not necessarily; it depends on the execution plan shape.

Result rows are assembled into packets for transmission to the client by the worker thread associated with the parent task. This thread will become blocked and wait on ASYNC_NETWORK_IO if the client is slow to consume results. Only the parent task's worker returns rows to the client.

Additional parallel worker threads active in other areas of the plan run independently, and have no direct knowledge of the state of the parent task. They can, generally, continue to execute even if the parent task's worker is waiting.

That said, these parallel workers will eventually produce rows that need to be consumed by the parent task. When exchange buffers become full, the parallel workers will also experience waits like CXCONSUMER or CXPACKET until the parent task's worker resumes and reads rows from the exchange.

For more background on the parent task and how parallel execution works see my articles:

• https://www.sql.kiwi/2020/07/how-maxdop-really-works.html
• https://sqlperformance.com/2013/10/sql-plan/parallel-plans-branches-threads
• https://sqlperformance.com/2021/03/sql-performance/parallel-plans-start-1

I've been trying to find the answer to this online for a while with no luck.

What are possible downsides or consequences to running an alter (such as add index, add column, drop index, drop column) with ALGORITHM=INPLACE, LOCK=NONE in mysql 8.0 innodb?

I figure there have to be some concerns or situations where you would not want to use LOCK=NONE at least.

If new rows are being added to the table as you run the alter to add an index, is it possible that some indexes wouldn't be created? I'm just wondering if I should be concerned about using these for all alters I run.

Thanks

When I dump the mysql db schema with phpmyadmin (5.2.0), I would like to get something like this:

CREATE TABLE `MEASURE_TABLE` (
  `measureID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `sensorID` int(10) UNSIGNED NOT NULL,
  `timestamp` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`measureID`),
  FOREIGN KEY (`sensorID`) REFERENCES `LOGICAL_SENSOR_TABLE` (`sensorID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Instead I get something like this (key defined via "ALTER TABLE"):

DROP TABLE IF EXISTS `MEASURE_TABLE`;
CREATE TABLE `MEASURE_TABLE` (
  `measureID` bigint UNSIGNED NOT NULL,
  `sensorID` int UNSIGNED NOT NULL,
  `timestamp` int UNSIGNED NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
ALTER TABLE MEASURE_TABLE

ADD PRIMARY KEY (measureID),

ADD KEY timestamp (timestamp),

ADD KEY sensorID (sensorID);
ALTER TABLE MEASURE_TABLE

ADD CONSTRAINT MEASURE_TABLE_ibfk_1 FOREIGN KEY (sensorID) REFERENCES LOGICAL_SENSOR_TABLE (sensorID);

I'm pretty sure they are equivalent, but for me the first version is much more readable. I had a previous schema dump from phpmyadmin (not done by me), that was in the first format. It was a different version though (4.6.6deb5).

I couldn't find the option in the export menu, is it possible to change it?

I've created a trigger for table x1 to update a column y with the expression to_tsvector(unaccent(x1.col1 || ' ' || x2.col1)). This trigger function throws:

function unaccent(text) does not exist

Why would this function not exist when a trigger is called, but exist when executed manually? I'm using Supabase to manage this database.

i have exported sql file using mysql dump from a database and i am trying to import this big 5gb file into anohter mysql database. I am using mysql -u username -p -h hostname

error : ERROR 2013 (HY000) at line 6950: Lost connection to MySQL server during query

i have added max_allowed_packet =1g and connect_timeout however still not able to import the big file.

If any one can share something that would be great

In an application with groups and posts where each post can be in exactly 1 group, we can have the table POSTS(post_id, group_id, post_content). However, there's gonna be a lot of posts in a lot of groups and filtering for posts in 1 or more groups can take a lot of resources. Instead, I think we can just create a new table $(GROUP_ID)_POSTS(post_id, post_content) where $GROUP_ID is the group_id from the POSTS table. I don't know about the specifics but I think joining several tables is gonna be a lot faster than filtering through a lot of group_ids.

Is this a good way to improve performance? Why or why not?

There are multiple ways and tools out there to do a database comparison. My preference is the tool https://www.sqlaccessories.com/sql-examiner/ . It is simple, powerful, relatively cheap, and can you tell you both schema differences and data differences. It's flexible in the advanced options by allowing you to ignore different things during comparison such as collation differences, defaults, check constraints, etc. And it can compare databases on the same server or across different servers.

Additionally, here's a https://www.mssqltips.com/sqlservertip/1069/sql-server-comparison-tools/ for database comparison, from MSSQLTips.

If you mostly only care about data differences, don't have a lot of tables to compare, and don't want to spend the money on a tool, you can probably roll your own solution using something like the https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15 or https://docs.microsoft.com/en-us/sql/t-sql/functions/checksum-transact-sql?view=sql-server-ver15 functions. I prefer HASHBYTES() for its https://sql-bits.com/sql-server-checksum-vs-checksum_binary-vs-hashbytes/ .

Leveraging one of the aforementioned functions, the idea would be to create a single hash per row (of the concatenation of every column in the table), and do a FULL JOIN on the hashes between the two instances of the table, filtering where either side's key is null. This would leave you with just the rows that are different.

How you want to implement the hashing function is your call. If it's a one time comparison, you can do it on the fly (though the join can be slow). If it's a recurring thing, you can add it as a column to the table itself or create a view on top of the table with it as a column, if you're able to alter the schema. This would allow you to create an index on the result of the function (HASHBYTES() is deterministic, I'm not so sure about CHECKSUM()), for better performance during comparison.

The limitation to this method is that both tables need to be accessible to be joined on, either by having both databases on the same server or by leveraging something like a https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine?view=sql-server-ver15 to access the other database's server, which potentially can be slow for a lot of data.

This is not supported by design.

Ref: https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql?view=sql-server-ver15

Specifies the statements inside the module execute in the context of the user specified in user_name. Permissions for any objects within the module are verified against user_name. user_name cannot be specified for DDL triggers with server scope or logon triggers. Use login_name instead.

user_name must exist in the current database and must be a singleton account. user_name cannot be a group, role, certificate, key, or built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

There are https://www.sqlservercentral.com/forums/topic/how-to-impersonate-an-ad-login-within-a-ad-group-defined-in-sql#:%7E:text=First%2C%20you%20can%20impersonate%20another,will%20run%20under%20their%20credentials. you can use.