Reply to: HELP - Agent has put me in a job overseas with no interview, and my skills don´t fit

[NotAllThere]

Well hmm I guess that SAP pays better than SQL, but two weeks experience, when I was working on the SQL Server not the SAP bit of it - would that count as "experience" as far as clients were concerned?

Only if you said "it was only two weeks". Perhaps you should speak to the client you were working for, and offer to do the training in exchange for a contract.

That's what I did to move from ABAP to SAP BI, thereby increasing my rate for £2000 investment (+ time off ).

[wendigo100]

Well done.

How is your pimping going?

[KentPhilip]

So what the answer?

Quite prosaic really. The 9-table query that was taking 90 seconds to run was actually using a bad execution plan. I ran dbcc indexdefrag first - no fix. Then I ran dbcc dbreindex- no fix.
Finally I ran alter index all on <tablename> rebuild with (ONLINE = ON)
This query ran and ran, and I eventually had to manually kill it. But half way through I ran the original query, and it took only 4 seconds to run, with a radically different query plan.

The tempdb never filled up during the time I was there - so a SAP index error they had received, that I got them to resolve, must have been the problem.

As for the third problem, not resolved yet, but I'm writing them a script for reindexing (on more than 1000 tables - eek!), and if that doesn't work, will suggest more drives in the data drive SAN to increase disk io.

[KentPhilip]

Well done! I suggest now you invest in a SAP Basis course. You've got the experience - so it's a good time to get the training.

Well hmm I guess that SAP pays better than SQL, but two weeks experience, when I was working on the SQL Server not the SAP bit of it - would that count as "experience" as far as clients were concerned?

[TimberWolf]

So what was the answer?

[NotAllThere]

Well I survived the short contract. Client now talking about possibly asking me back in the new year, but nothing definite.

What I did was to get on to rentacoder.com and post my problems there. I got a reply from a UK-based experienced SQL Server DBA, and he spent a couple of hours on the skype phone in the evening at the client going through and sorting the problems. Only 25 quid and hour he charged - he was trying to operate a business for multiple clients to escape IR35, so a win-win situation all round it seems.

Of course the way I played it with the client was that I was part of a consultancy, and "the service we offer you consists of input from me and our other experienced consultants"...

Well done! I suggest now you invest in a SAP Basis course. You've got the experience - so it's a good time to get the training.

[BrowneIssue]

Well I survived the short contract. Client now talking about possibly asking me back in the new year, but nothing definite.

Sorted.

[Diver]

Well played. But time to bone up on it yourself now, don't you think

[KentPhilip]

Well I survived the short contract. Client now talking about possibly asking me back in the new year, but nothing definite.

What I did was to get on to rentacoder.com and post my problems there. I got a reply from a UK-based experienced SQL Server DBA, and he spent a couple of hours on the skype phone in the evening at the client going through and sorting the problems. Only 25 quid and hour he charged - he was trying to operate a business for multiple clients to escape IR35, so a win-win situation all round it seems.

Of course the way I played it with the client was that I was part of a consultancy, and "the service we offer you consists of input from me and our other experienced consultants"...

[Zorba]

There are but they don't like you mentioning it as it is something of a goldmine for them.

Nonsense. Besides, what he should be doing is rewriting it all in .Net. It's the future.

[TimberWolf]

I haven’t used these products but is there an execution plan, and if so is it built some time before execution, or just prior? I’m wondering whether an execution plan might be different (carp) if run against small test datasets rather than a huge live one. That is, the order the joins are done might affect performance significantly, and I don’t recall a method of forcing the issue in SQL.

[KentPhilip]

Forget the query analyser. Just use your eyes. Look at the query and see what columns are used in any joins and plonk an index on them if they don't have one. It's not exactly rocket science.

There are 9 tables in the select query. I've checked and they all have indexes on the join columns and where columns.
If I cut the query down to three tables, it returns the results very fast. If I add a fourth table it takes 7 seconds but it is a cross product so the time is probably explained by having to return 2000000 rows.
Maybe SQL 2005 is a crap product that takes two minutes to join 9 large tables.

[Cowboy Bob]

When I ran the offending query in Query analyser it said that it could not find any of the 10 tables referenced in the query. So I'm a bit stuffed.

Forget the query analyser. Just use your eyes. Look at the query and see what columns are used in any joins and plonk an index on them if they don't have one. It's not exactly rocket science.

[Weltchy]

Thanks for the replies.

The client agrees that more hard drive space is required, so I'm on to my second challenge = which is a query that is taking 55 seconds to complete. SQL Profiler shows that it does 23 million page reads, so it is obviously doing a table scan and not using an index correctly.
When I ran the offending query in Query analyser it said that it could not find any of the 10 tables referenced in the query. So I'm a bit stuffed.
I have a hunch that this OLAP thingy used by SAP works a little different from transact SQL.
Also been yawning too much all day. I'm not cut out to be a contractor (oh hang on I've just left my permie job to become one...)

Just left permie job......I see......Did you change to the right database. The dropdown list is located at the top left!!!! Or use "Use [Insert Database Name Here]"

[KentPhilip]

Thanks for the replies.

The client agrees that more hard drive space is required, so I'm on to my second challenge = which is a query that is taking 55 seconds to complete. SQL Profiler shows that it does 23 million page reads, so it is obviously doing a table scan and not using an index correctly.
When I ran the offending query in Query analyser it said that it could not find any of the 10 tables referenced in the query. So I'm a bit stuffed.
I have a hunch that this OLAP thingy used by SAP works a little different from transact SQL.
Also been yawning too much all day. I'm not cut out to be a contractor (oh hang on I've just left my permie job to become one...)