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

SQL Server, first of each time series
Connection Waiting For SQL Remote Connection Freeze My App
Using BCP and xp_cmdshell with T-SQL inside SSMS. “The syntax of the command is incorrect”
SQL Server triggers to check credit card balance
How to use stored procedure to calculate DateTime difference in hours?
Loop through table by row T-SQL
Audit Microsoft SQL Server table with many columns
Select joining table based on condition
Linked server with same server name
How to group by two dates to sum an quantity
Retrieving record to a combo box in vb.net
Need select 2 rows from Table2, which is joined with Table1. See description
Can't add a connection in Visual Studio: no server name available
Average of a date column in sql [duplicate]
How to get the previous row value with an ORDERING BY DATE?
How to disable/enable full text indexed in SQL Server using dynamic SQL?

Categories

HOME
swift
sidekiq
mql4
crate
oauth
scipy
boost-thread
cocos2d-x-3.0
transparent
jpanel
avl-tree
vsftpd
cartodb
nixos
ctypes
qt-installer
store
symfony2-easyadmin
react-leaflet
redux-observable
ada
backpropagation
flexboxgrid
excel-2010
url.action
iup
memsql
npm-install
geopandas
chrome-native-messaging
ggmap
gpib
vegan
hotmail
shapes
nsurlconnection
phpspreadsheet
large-data
honeysql
google-guava-cache
oscommerce
color-scheme
es-shell
discord.net
elfinder
jrules
resuming-training
ivona
uiviewpropertyanimator
gauss
istorage
applepayjs
dwarf
udev
hpcc
collapsingtoolbarlayout
bettercms
pdfkit
picasa
unsatisfiedlinkerror
com-interop
brython
remoteapp
flexjson
asp.net-mvc-2
mirrorlink
python-winshell
subresource-integrity
paypal-nvp
programming-paradigms
risk-analysis
uos
callstack
shift-jis
kendo-mvvm
omnifocus
vcl
funq
smartystreets
colt
pic24
unison
initialization-vector
sim900
cling
opendata
osx-snow-leopard
html-form-post
entity-framework-4.1
backbone-relational
cgimageref
sharpmap
soa-suite
pitch
vim-powerline
xmlslurper
rose-db-object
visual-web-gui
entity-framework-ctp5
driving-directions
nsindexpath
surf
camtasia
code-design
callgrind
handheld
castle-validators
weborb
interface-design

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