Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««4,3684,3694,3704,3714,372»»»

Are the posted questions getting worse? Expand / Collapse
Author
Message
Posted Wednesday, April 9, 2014 6:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 10,381, Visits: 13,436
SQLBill (4/8/2014)
Ville-Pekka Vahteala (4/8/2014)
SQLBill (4/8/2014)
Jack Corbett (4/6/2014)
So on Thursday I was asked to help analyze a batch process for a third-party product that originally took 2 hours and is now 19+ hour process that sometimes fails. No escalating waits, no obvious metrics out of whack, just a long running process. It looks like there are client-side cursors to do aggregation and then insert aggregate data. I haven't totally verified that yet. The best part is that the application is a Java application which likely means hibernate (that's what the queries look like to me). The best part is that JDBS drivers default to sending all string data in Unicode (NVARCHAR(4000)) and the database is all char/varchar. Every query that has a sting parameter has a CONVERT_IMPLICIT(NCHAR(N), column) = @nvarcharparameter. So every query is either doing an index seek on an index based on numeric parameters followed by a key lookup, or a clustered index scan because no indexes are covering indexes. I tested a few of the queries, just changing NVARCHAR(4000) to VARCHAR(4000) and in each test the plan went to a clustered index seek because all the parameters in the query are on columns in the clustered index, and each query returns one row. The best part is that it can be fixed by adding an attribute to the connection string that will pass string parameters as varchar. I can't make the change, I can only recommend the change to the vendor.

I was told that one of their early comments is that our database is too big. I think it is around a 100GB and the biggest table has about 3.7 million rows.


Isn't it funny how it's always "the database is too large"? I had an issue where we had a 3rd-party product that did intrusion detection. When we were testing it, we found that it was taking a long time (over a day) to delete a couple of day's data. I looked at the code and found that they:
1. copied the event id of all the applicable rows to a temporary table.
2. numbered the rows one-up.
3. via loop, deleted 1000 rows.
4. dropped the temporary table (yep...all the rows haven't been deleted yet...just the first 1000)
5. started again at #1.

I suggested that after step 2, they find the max row number, put that into a variable and have the loop delete every thousand rows until the max row number was met. Deletes taking over a day were down to seconds.

But they first wanted to blame it on the amount of data we were collecting and having to delete.

-SQLBill


But isn't it true that in both cases your data is more than their application can handle? If the data would be smaller 3rd party application would run just fine :)
I have been in the posission of vendor when we customized our application to one client and all went well until publishing it to production with real data. Same application had been working just fine for years already, but in their environment it was very slow and and using too much CPU. All came to fact that they had 200k rows in one table compared to other client's 20. Execution plans were full of table scans and optimizer was generating plans opposite way.


Naw....their application could handle the data....it just wasn't designed to do so. Their developer was cool about it. After cussing me out (because it was obvious he should have considered that when coding)...he laughed and got the change put in. Even thanked me for it. They had this working with a bunch of companies...but no one else had ever needed to delete more than 1000 rows...until us.

-SQLBill


SO this vendor is not willing to share code because it is "complex" sql. Basically everything is RBAR in so our database is "too big". The suggestions I made, not including changing the app code, took the process from 45 hours to 20 hours. The answer we got from them, because they had it running in 4 against our data on their server is throw more resources at it. So we doubled RAM (8-16) and CPU (4 cores to 8 cores) in our test environment to match their test environment (we are on a VM) and matched their results. So the process is now acceptable to the business folks. I'm still ticked that we got stuck with a crappy application.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1559904
Posted Wednesday, April 9, 2014 8:10 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:52 PM
Points: 748, Visits: 5,518
How to have a day go from "going OK" to "I'm almost unable to see straight I'm so ticked off."

Find out from your wife that sometime between you leaving for work, and her leaving for work, that someone keyed the drivers side of her car (which was parked outside) and keyed "f**k you" on the hood...

Because of "stuff" in the garage, her car has to be parked in the drive, and it's right by the sidewalk, no fence. I *suspect* it was a punk-**s kid who thought it would be funny while waiting for the school bus at the stop across the street, but have no way to prove it (no security cams on the house.)

No kids there when I left, they start showing up around 6:30 and I was gone by 5:30 this morning...


OK, done venting.
Post #1559967
Posted Wednesday, April 9, 2014 8:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,325, Visits: 12,811
jasona.work (4/9/2014)
How to have a day go from "going OK" to "I'm almost unable to see straight I'm so ticked off."

Find out from your wife that sometime between you leaving for work, and her leaving for work, that someone keyed the drivers side of her car (which was parked outside) and keyed "f**k you" on the hood...

Because of "stuff" in the garage, her car has to be parked in the drive, and it's right by the sidewalk, no fence. I *suspect* it was a punk-**s kid who thought it would be funny while waiting for the school bus at the stop across the street, but have no way to prove it (no security cams on the house.)

No kids there when I left, they start showing up around 6:30 and I was gone by 5:30 this morning...


OK, done venting.


UGH!!! That really sucks.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1559971
Posted Wednesday, April 9, 2014 8:28 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 20,857, Visits: 32,877
OMG!

yuvipoy (4/9/2014)
I have been using this statement for nearly 6 years in prodution i did not get any error or issue.
all of the sudden it happened today and i am unable to resolve the issue, still searching for the reason.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1559981
Posted Wednesday, April 9, 2014 8:28 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:52 PM
Points: 748, Visits: 5,518
Sean Lange (4/9/2014)
jasona.work (4/9/2014)
How to have a day go from "going OK" to "I'm almost unable to see straight I'm so ticked off."

Find out from your wife that sometime between you leaving for work, and her leaving for work, that someone keyed the drivers side of her car (which was parked outside) and keyed "f**k you" on the hood...

Because of "stuff" in the garage, her car has to be parked in the drive, and it's right by the sidewalk, no fence. I *suspect* it was a punk-**s kid who thought it would be funny while waiting for the school bus at the stop across the street, but have no way to prove it (no security cams on the house.)

No kids there when I left, they start showing up around 6:30 and I was gone by 5:30 this morning...


OK, done venting.


UGH!!! That really sucks.


Oh yeah. At least there's only a $200 deductible on the car insurance for this sort of damage, and she's taking it to the dealership repair shop (which is right by her work) for an estimate on the repairs.

Saturday we're renting a pickup truck and hauling away the crap that's keeping us from getting both cars in the garage, but still...

Makes me glad I'm moving out to a nicer area in the fall...
Post #1559982
Posted Wednesday, April 9, 2014 8:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,325, Visits: 12,811
Lynn Pettis (4/9/2014)
OMG!

yuvipoy (4/9/2014)
I have been using this statement for nearly 6 years in prodution i did not get any error or issue.
all of the sudden it happened today and i am unable to resolve the issue, still searching for the reason.



I am not sure which part of this is more scary, the fact that (s)he has been using NOLOCK through an ODBC connection for 6 years or that with at least 6 years experience (s)he doesn't have a better grasp of the language.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1559986
Posted Wednesday, April 9, 2014 9:10 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 18,060, Visits: 16,090
jasona.work (4/9/2014)
How to have a day go from "going OK" to "I'm almost unable to see straight I'm so ticked off."

Find out from your wife that sometime between you leaving for work, and her leaving for work, that someone keyed the drivers side of her car (which was parked outside) and keyed "f**k you" on the hood...

Because of "stuff" in the garage, her car has to be parked in the drive, and it's right by the sidewalk, no fence. I *suspect* it was a punk-**s kid who thought it would be funny while waiting for the school bus at the stop across the street, but have no way to prove it (no security cams on the house.)

No kids there when I left, they start showing up around 6:30 and I was gone by 5:30 this morning...


OK, done venting.


That really sucks. Some day people just need to grow up a bit.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1560014
Posted Wednesday, April 9, 2014 9:11 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 18,060, Visits: 16,090
Sean Lange (4/9/2014)
Lynn Pettis (4/9/2014)
OMG!

yuvipoy (4/9/2014)
I have been using this statement for nearly 6 years in prodution i did not get any error or issue.
all of the sudden it happened today and i am unable to resolve the issue, still searching for the reason.



I am not sure which part of this is more scary, the fact that (s)he has been using NOLOCK through an ODBC connection for 6 years or that with at least 6 years experience (s)he doesn't have a better grasp of the language.


Yeah that is a wee bit scary.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1560016
Posted Wednesday, April 9, 2014 12:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:36 AM
Points: 31,362, Visits: 15,823
Adding and bumping topics in here: http://www.sqlservercentral.com/Forums/Forum2824-1.aspx

Please don't commit to more than one and please don't add in random comments. I'd like to try and keep track of these articles.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1560140
Posted Wednesday, April 9, 2014 12:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:36 AM
Points: 31,362, Visits: 15,823
Jack Corbett (4/9/2014)


SO this vendor is not willing to share code because it is "complex" sql. Basically everything is RBAR in so our database is "too big". The suggestions I made, not including changing the app code, took the process from 45 hours to 20 hours. The answer we got from them, because they had it running in 4 against our data on their server is throw more resources at it. So we doubled RAM (8-16) and CPU (4 cores to 8 cores) in our test environment to match their test environment (we are on a VM) and matched their results. So the process is now acceptable to the business folks. I'm still ticked that we got stuck with a crappy application.


Maybe a writeup will be cathartic? It would be interesting to have you mock and discuss the changes you made, and the alternative of cranking up hardware (with some cost estimate)







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1560141
« Prev Topic | Next Topic »

Add to briefcase «««4,3684,3694,3704,3714,372»»»

Permissions Expand / Collapse