Click here to Skip to main content
15,860,972 members
Home / Discussions / Database
   

Database

 
QuestionMoving from access DB to Oracle to calculate average upon request Pin
Member 1447460719-Sep-19 11:12
Member 1447460719-Sep-19 11:12 
AnswerRe: Moving from access DB to Oracle to calculate average upon request Pin
Gerry Schmitz19-Sep-19 12:26
mveGerry Schmitz19-Sep-19 12:26 
QuestionMySQL database Pin
Alboyz17-Sep-19 16:46
Alboyz17-Sep-19 16:46 
AnswerRe: MySQL database Pin
#realJSOP18-Sep-19 1:08
mve#realJSOP18-Sep-19 1:08 
GeneralRe: MySQL database Pin
Alboyz18-Sep-19 16:58
Alboyz18-Sep-19 16:58 
AnswerRe: MySQL database Pin
Mycroft Holmes18-Sep-19 12:44
professionalMycroft Holmes18-Sep-19 12:44 
GeneralRe: MySQL database Pin
Alboyz18-Sep-19 16:58
Alboyz18-Sep-19 16:58 
QuestionDatabase suddenly slow Pin
Super Lloyd17-Sep-19 15:39
Super Lloyd17-Sep-19 15:39 
One of our reporting database is becoming mysteriously slow in the last 2 weeks. While it was working perfectly fine before that.

There was 2 tables with about 10 millions records each and it was working fine.
Now I have 3 tables with about 10 millions records (dunno if it change anything) bu t I am starting to have plenty of Timeout exception! Blush | :O

Case in point
This is simplest EF call:
C#
(
    from ite in this.ItemTransactions
    where ite.DataSourceId == 3
    orderby ite.ItemTransactionId descending
    select new { ite.PrimaryIdInDataSource }
).FirstOrDefault()

and it generates this simple SQL
SQL
-- Region Parameters
DECLARE @p0 Int = 3
-- EndRegion
SELECT TOP (1) [t0].[PrimaryIdInDataSource]
FROM [ItemTransaction] AS [t0]
WHERE [t0].[DataSourceId] = @p0
ORDER BY [t0].[ItemTransactionId] DESC

Now it takes 2 minutes to run that SQL code in SQLSMS, which is suspiciously slow for only 6 millions records.
Also, when I run my C# code, EF timeout! Even thought I set the command timeout to be 10 minutes! Blush | :O

Most puzzling all this code was running fine 2 weeks ago... Can't find the reason in the TFS history.. Unsure | :~

--

[EDIT: Found it]

Between 2 recreations and data repopulation of the database.. someone one changed the primary key of many tables that were used in joins from CLUSTERED to NONClUSTERED index

That seemed like a good idea at the times, they said.... Poke tongue | ;-P
A new .NET Serializer
All in one Menu-Ribbon Bar
Taking over the world since 1371!


modified 19-Sep-19 2:48am.

AnswerRe: Database suddenly slow Pin
CHill6018-Sep-19 0:05
mveCHill6018-Sep-19 0:05 
GeneralRe: Database suddenly slow Pin
Super Lloyd18-Sep-19 20:47
Super Lloyd18-Sep-19 20:47 
AnswerRe: Database suddenly slow Pin
Richard Deeming18-Sep-19 1:01
mveRichard Deeming18-Sep-19 1:01 
GeneralRe: Database suddenly slow Pin
Super Lloyd18-Sep-19 20:47
Super Lloyd18-Sep-19 20:47 
GeneralRe: Database suddenly slow Pin
Mycroft Holmes19-Sep-19 13:14
professionalMycroft Holmes19-Sep-19 13:14 
AnswerRe: Database suddenly slow Pin
Member 1240381729-Dec-19 23:35
Member 1240381729-Dec-19 23:35 
QuestionI need this windows odbc driver Pin
Member 1458760611-Sep-19 8:00
Member 1458760611-Sep-19 8:00 
AnswerRe: I need this windows odbc driver Pin
OriginalGriff11-Sep-19 8:01
mveOriginalGriff11-Sep-19 8:01 
GeneralRe: I need this windows odbc driver Pin
Member 1458760611-Sep-19 8:16
Member 1458760611-Sep-19 8:16 
AnswerRe: I need this windows odbc driver Pin
Maciej Los11-Sep-19 8:47
mveMaciej Los11-Sep-19 8:47 
QuestionLooking for some assistance with a query Pin
FrankLepkowski10-Sep-19 9:50
FrankLepkowski10-Sep-19 9:50 
AnswerRe: Looking for some assistance with a query Pin
MadMyche10-Sep-19 11:12
professionalMadMyche10-Sep-19 11:12 
GeneralRe: Looking for some assistance with a query Pin
MadMyche11-Sep-19 1:56
professionalMadMyche11-Sep-19 1:56 
GeneralRe: Looking for some assistance with a query Pin
FrankLepkowski11-Sep-19 4:15
FrankLepkowski11-Sep-19 4:15 
GeneralRe: Looking for some assistance with a query Pin
MadMyche11-Sep-19 6:28
professionalMadMyche11-Sep-19 6:28 
QuestionNeed help mixing two queries in one... Pin
Joan M1-Sep-19 0:40
professionalJoan M1-Sep-19 0:40 
AnswerRe: Need help mixing two queries in one... Pin
Richard Deeming2-Sep-19 1:02
mveRichard Deeming2-Sep-19 1:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.