Tag: performance

Inconsistent query speed between LINQ and (sometimes) SQL

I have a strange inconsistenty in query speed with linq to sql functions. I have a function which I call from an MVC application. This is invariably extremely slow, in the order of 7 seconds. When I call the same function from SQL management studio, it is sometimes slow, and sometimes fast (a fraction of […]

Options to replace slow SQL generated by Entity Framework 6

I’ve been using Entity Framework 6 code first to do some simple CRUD operations on my domain model and it’s performed admirably so far. I’ve now come across a situation where I’m performing a reasonably complicated query which involves filtering and paging of the results. The query generated by EF 6 is pretty bad and […]

Conditional aggregate database queries and their performance implications

I think this question is best asked with an example: if you want two counts from a table – say one with all the rows with a bit flag set to false and another with all of the ones set to true – is there a best practice for this kind of query and what […]

How to connect multiple “highest in this interval” query calls into one query?

Suppose we have (stripped) entities: Message { int MessageID; int DeviceID; DateTime Timestamp; string Value; }; Device { int DeviceID; string name; byte Type; }; We’ve got 10 000 devices, every one sends up to 5 status messages per minute for two years, what goes into DB. Now we would like, for lets say – […]

Embedded SQL Count is taking too much to proceed

Here is my SQL command : SELECT ts.CHECK_NUMBER, ts.CUSTOMER_NAME, ts.COMPANY_NAME, ( SELECT COUNT(*) FROM TRANSACTION_ORDER too WHERE too.CHECK_NUMBER = ts.CHECK_NUMBER ) as NB_OF_ORDERS FROM TRANSACTION_SUMMARY ts ORDER BY ts.BUSINESS_DATE It is taking so long to render data, we are talking about minimum 3000 transactions, for each one we have to count the orders. Is there […]

SQL Server Nested Query Performance

I need some help writing this query for SQL Server. The nested part makes this query take almost a minute to run on 27,000 records. I think it needs a temp table, but I have not done this before. Can someone give me an idea how I might do this? SELECT r.ID, r.CloseDate, r.RepairOrderStatus ‘Repair […]

Simple SQL query performance puzzles me

A quick note: We’re running SQL Server 2012 in house, but the problem seems to also occur in 2008 and 2008 R2, and perhaps older versions as well. I’ve been investigating a performance issue in some code of ours, and I’ve tracked down the problem to the following very simple query: SELECT min(document_id) FROM document […]

How do you display the SQL statement of a Linq-To-SQL query?

I’m trying to retrieve the earliest DateTime value that has a specific date. The Holder instance has already been retrieved from the data context (without prefetching), now I need to search through the IO EntitySet (that has not been fetched). I’m doing this in a foreach loop for each holder so it takes a long […]

Poor SQL Server performance on virtual machine after system upgrade

I’m managing middle sized company database. Recently we have massive system upgrade and switched to virtual machine environment. After deployment, we are having performance issues, Apparently SQL Server on the upgraded system works slower than the old configuration. Here are the configurations: Old Server: SQL Server 2008, 10gb Ram, Intel Xeon E5420 x2 (Physical Machine) […]

SQL inserts slow – “pre populate with blank rows??”

I have an .exe that compares a vbTab delimited .txt file with an SQL table. Updates to the table’s existing records goes very fast. Inserts into the table for new records is quite slow. As I’m new to SQL, I’m wondering if my idea is crazy talk: I thought that maybe a solution would be […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.