SQLTeam.com | Weblogs | Forums

Database Design SQL server

are you there ?

Hi Scott,

Thanks for sharing with me your skills. I think you are great and hope we can engage more into this.

Sorry, been very busy. I couldn't get the thing to load as xml; I'm not at all any type of expert on XML, just on data base design and coding.

It’s Ok, I still value your skills and engaging with you. I still think you are great, and admire you. Can you look at the image I had in my previous post rather than the xml codes. Take your time, I am not in a hurry or rush. If this is end of this topic is Ok too. I guess we can speak about other topics that are similar when I or you post again. Thx

Copy the xml above to notepad and then save as johnse.sqlplan then following below

  1. In SQL Server Management Studio, on the File menu, choose Open , and then click File .
  2. In the Open File dialog box, set Files of type to Execution Plan Files (*.sqlplan) to produce a filtered list of saved XML query plan files.
  3. Select the XML query plan file that you want to view, and click Open .As an alternative, in Windows Explorer, double-click a file with extension .sqlplan . The plan opens in Management Studio.

But isn't something missing from the file above? It starts with a ">", not an XML "identifier line".

You might have created that part, but I don't know how to do that. Or can the xml start at "<ShowPlanXML ...": I didn't think that was valid, but maybe it is?!

yes clean that first ">" and you should be good.

I thought that <?xml...?> was required? If not, I'll try that. Again, xml NOT my area of expertise.

OK, got it. That's an estimated plan only, so it doesn't show the actual row counts.

If there are a lot of rows in table "A1"/"a", then force hash joins rather than a loop join with a seek for every row:

SELECT *
FROM dbo.A1 a
INNER HASH JOIN dbo.H1 h ON a.d = h.d and a.Name = h.Name
INNER HASH JOIN dbo.R1 r ON a.d = r.d and a.Name = r.Name
WHERE a.d = '2015-01-01' and a.name = 'Record' and r.prcd = 'am1'

If that performs noticeably better, it's safe to leave the hint in. A HASH join won't be disastrous with just a few rows, but with a huge number of rows a loop join can perform horribly.

xml is ubiquitous, worth adding that to your cap. it is honestly very simple.

JSON is preferred in our shop. It's whatever you prefer, really.

1 Like

For what it's worth, for some reason the developers in our shop hate xml and, thus not surprisingly, strongly prefer JSON (pronounced "jason" now, apparently).

I frankly don't care either way, that's not my area of expertise nor concern.

1 Like

We use both. At the end of the day it's all strings to me

OP / @Johnse:

Did the HASH join hint work for you? Or does something still need done?

Let me check and get back to you.

Unfortunately I tried logging onto my SQL server but is having some issues. Not sure why, working on the access then I could let you know when I do. Might end up needing to reinstall Mgment Studio unless someone know how I can fix it.

Did the HASH join hint work for you? Or does something still need done?

Actually tried it, and it doesn't look like there is any difference between the HASH JOIN and none HASH JOIN. Its 00 seconds as below with 10 rows for both. It may be because is not a complex query or large output of data from the query.

0000

In this tutorial, you will draw a simple ERD of an online bike shop, define sample data for your database and generate database to Microsoft SQL Server will be used in this tutorial.
https://www.visual-paradigm.com/tutorials/sdevsgendb.jsp
https://www.mssqltips.com/sql-server-tip-category/10/database-design/
MS SQL Training in Noida
Oracle Developer Training in Delhi