sql-server


What is the Database impact: Querying a table with filter on two colums OR table with twice the records with filter on one single column?


Which has more database performance impact?
Querying a short table (say 20 Million records) with filter on two columns
or
querying a long table twice as more records than the short table with filter on one column?
To give some context:
I have a simple non-directed, connected graph about people and their relationships to other friends/people. The BI end user wants to search for a "person", and then use the fact table to identify all the related people/friends.
I am building a fact table (Star Schema) to save the information of the graph. The grain of my fact table is a Single relationship at one degree (direct relationship), supported by Date and a Person dimension. The person of interest can be in Node_1_person or Node_2_Person column.
Table Definitions:
RELATIONSHIP FACT TABLE (~40 Million Rows)
Relationship_ID
Node_1_person
Node_2_Person
Relationship_Strength_Score
Relationship_Counter
Relationship_Created_Date
Person_Dimension (~20 Million Rows)
Person_Surrogate_Key
Person_Natural_key
Person_Name
Person_Address
Person_Email
I have two options to facilitate the requirement:
Option #1: I save the reciprocal of the relationship, so the end user can search one column to find the relationship.
Option #2: I avoid reciprocal relationships, and then the end user has to look into both Node_1_person and Node_2_Person column.
I not sure, which option will have more performance impact to the database.
Why not test both queries? Depending on how fast your database is, testing shouldn't take too long..

Related Links

When rowversion column is calculated on SQL Server
What's the columns updating order when several columns are updated?
Failed to open rowset. Details:42000:[Microsoft][ODBC SQL Server Driver][SQL Server]
unique key on two columns commutatively
SQL Count with Inner Join for Single Table
How to use stored procedure with table-valued parameters to compare two input tables
Get items and dates checked in and out by person
SQL Anywhere 16 to SQL Server Migration
SQL Server : reverse engineering view and stored procedure
I imported a CSV into SQL Server and am now stuck with these hideous pipe characters
Reporting Services Configuration Manager - empty instance name
Azure. Automation of tempDB with ARM templates
SQL Server Inequality Comparison Operators, why have two [duplicate]
Sql Server Msg 2110
SQL Server - two joins to the same table on the same column
Queries with Dynamic Parameters - better ways?

Categories

HOME
mql4
npm
websphere
textwatcher
decorator
label
cocos2d-x-3.0
codeeffects
rfc
hyperledger-fabric
histogrammar
websphere-liberty
sudo
dropbox
flexbox
future
jquery-terminal
bar-chart
scenebuilder
redux-observable
flat-file
jpeg2000
linq-to-sql
ejbca
fabric
visual-c++-2017
hanami
rhmap
owl-api
vertex-buffer
configure
positioning
jboss5.x
consumer
backup-strategies
pycparser
qtablewidget
precedence
geomesa
sidr
shibboleth
code-behind
common.logging
onbackpressed
stdclass
code-composer
mediawiki-extensions
productivity
bing-translator-api
spring-bean
bids
timesten
tuleap
sapui
jenkins-jira-trigger
crash-reports
castle-dynamicproxy
sqlexception
windows-azure-pack
galleriffic
mix
spark-cassandra-connector
spring-repositories
proxygen
fortran90
android-recyclerview
retro-computing
textkit
leadtools-sdk
crash-dumps
gulp-livereload
c++-actor-framework
cloudpebble
ibmsbt
adcolony
sortable
dto
angulartics
nimrod
image-zoom
dllexport
javascriptmvc
dwolla
sharpmap
resgen
htmltextwriter
web-safe-fonts
qsqltablemodel
moq-3
responsetext
jquery-selectbox
msgbox
cross-domain-policy
zookeeper
surf
adobe-contribute
revert
boost-date-time
jquery-ui-button
lazy-c++
outlook-form

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App